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!
Code:
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