VBA Macro to compare 2 sheets for only specified rows

[davidb88]

Hi - I have found a number of great posts that show how to compare 2 spreadsheets and highlight the differences. I would like to be able to do something a bit different, because my 2 spreadsheets are not 1 for 1 matches, especially with the number of rows there are. I would like to find a macro to compare "Sheet2" to "Sheet1" for rows where the unique ID in Sheet1 can be found on sheet2. So basically, I want the macro to first locate the corresponding unique ID from sheet 1 on sheet 2, and then once it does that, compare the rest of the columns for that same unique Id and highlight the differences and then loop through all of the unique IDs on sheet1 to try to find them on sheet2. Is that possible? This is what I was using to just do a complete sheet comparison, but because sheet1 has more rows than sheet2 I can't get the 2 sheets to match enough to get a valid comparison. Any help or suggestions would be appreciated. Thanks!

Sub RunCompare()

Call compareSheets("Sheet1", "Sheet2")

End Sub


Sub compareSheets(shtSheet1 As String, shtSheet2 As String)

Dim mycell As Range
Dim mydiffs As Integer

For Each mycell In ActiveWorkbook.Worksheets(shtSheet2).UsedRange
    If Not mycell.Value = ActiveWorkbook.Worksheets(shtSheet1).Cells(mycell.Row, mycell.Column).Value Then
        
        mycell.Interior.Color = vbYellow
        mydiffs = mydiffs + 1
        
    End If
Next

MsgBox mydiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtSheet2).Select

End Sub

 

[JLGWhiz]

Assuming unique IDs are in column A on both sheets.

Sub sidy()
Dim sh1 As Worksheet, sh2 As Worksheet, fn As Range, i As Long, col As Long, c As Range
Set sh1 = Sheets("Sheet1") 'Edit sheet name
Set sh2 = Sheets("Sheet2") 'Edit sheet name
    For Each c In sh1.Range("A2", sh1.Cells(Rows.Count, 1).End(xlUp))
        Set fn = sh2.Range("A:A").Find(c.Value, , xlValues, xlWhole)
            If Not fn Is Nothing Then
                col = sh2.Cells(fn.Row, Columns.Count).End(xlToLeft).Column
                For i = 2 To col
                    If sh1.Cells(c.Row, i).Value <> sh2.Cells(fn.Row, i).Value Then
                        sh1.Cells(c.Row, i).Interior.ColorIndex = 6
                        sh2.Cells(fn.Row, i).Interior.ColorIndex = 6
                    End If
                Next
            End If
    Next
End Sub

If your unique Id is not in column A of each sheet the code fails and you will need to modify the code to accomodate the actual sheet layout. Posting this type of information in the original post will save time and effort in resolving your issues.

 

[davidb88]

Hi JLGWhiz,

Thank you so much. This worked. The unique ID is in Column A for both sheets. Sorry for not clearly outlining that in the original post. Thanks again! You saved me a lot of time trying to figure this out via trial and error!

 

[JLGWhiz]

Hi JLGWhiz,

Thank you so much. This worked. The unique ID is in Column A for both sheets. Sorry for not clearly outlining that in the original post. Thanks again! You saved me a lot of time trying to figure this out via trial and error!

You're welcome,
Regards, JLG