Find differences between 2 excel sheets or tables

Hi,

I am trying to find differences between 2 super large excel sheets. I have a table in Sheet 1 and an almost identical table in Sheet 2. Sheet 2's table has cells with changed or deleted values though. I want to highlight or know about changes between Sheet 2 and 1. I found the excel VBA code below on the internet:

Sub RunCompare()

Call compareSheets("Sheet1", "Sheet2")

End Sub


Sub compareSheets(shtSheet1 As String, shtSheet2 As String)

Dim mycell As Range
Dim mydiffs As Long

'For each cell in sheet2 that is not the same in Sheet1, color it yellow
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

'Display a message box to demonstrate the differences
MsgBox mydiffs & " differences found", vbInformation

ActiveWorkbook.Sheets(shtSheet2).Select

End Sub

It almost works. The problem is that it highlights way too many cells whenever a row in Sheet 2 will have disappeared compared to Sheet 1. The code above compares the 2 sheets with respect to the content of cells relative to their position in the sheet (e.g. Cell B18 in Sheet 1 == Cell B18 in Sheet 2??). I would like it to compare the values in the 2 tables based on the ID number in the very left column of both tables. That way, if a row is deleted in the table from sheet 1 to sheet 2, I will not have all cells highlighted in Sheet 2 from that deleted row downwards.

I attached a simplified version of my excel sheet tables. The real one has thousands of rows. test1.xlsm

Thanks!

FBG

Hi, welcome to the forum

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I have added them for you - this time

OK, having said that...

How and where do you want the differences shown? Can you provide a sample of how you want them shown please?