How to Compare Two Excel Workbooks for Differences

From time to time, we are required to compare two Excel sheets and highlight the differences. In order to save me time, I developed a VBA macro to do the task.

I created following two simple dummy Excel files to facilitate your understanding. Both of them are saved under C:\Example. 

It’s a good habit to make VBA macro get data from worksheet cell. And here is a picture which helps you to understand this concept. You can just change column B without having to update VBA code every time you need to compare two new Excel files.

From the below code in red, I guess that you can figure out how it works.

Sub Compare_Click()     'Define variable and object     Dim wbkA As Workbook     Dim wbkB As Workbook     Dim varSheetA As Variant     Dim varSheetB As Variant     Dim iRow As Long     Dim iCol As Long         'Open C:\Example\Class 1_Lilly.xls     Set wbkA = Workbooks.Open(ThisWorkbook.Worksheets(1).Cells(1, 2))     Set varSheetA = wbkA.Worksheets("Class 1")         'Open C:\Example\Class 1_Lucy.xls     Set wbkB = Workbooks.Open(ThisWorkbook.Worksheets(1).Cells(2, 2))     Set varSheetB = wbkB.Worksheets("Class 1")        'Get the last row with data     a = varSheetA.UsedRange.Rows.Count     'Get the last column with data     b = varSheetA.UsedRange.Columns.Count     'Loop through all cells     For iRow = 1 To a         For iCol = 1 To b             If varSheetA.Cells(iRow, iCol) <> varSheetB.Cells(iRow, iCol) Then                varSheetA.Cells(iRow, iCol).Interior.Color = RGB(55, 242, 251)                varSheetB.Cells(iRow, iCol).Interior.Color = RGB(55, 242, 251)             End If         Next iCol     Next iRow         'Close both Excel files and save changes     wbkA.Close SaveChanges:=True     wbkB.Close SaveChanges:=True End Sub

Finally, I will show you the result. You can see that all differences are highlighted in blue. This approach is really helpful especially when there are a lot of data.

But did you notice that these two Excel sheets have the same number of rows and columns? What if the numbers of rows vary? Below is the full program to solve this kind of problem.

Sub Compare_Click()     'Define variable and object     Dim wbkA As Workbook     Dim wbkB As Workbook     Dim varSheetA As Variant     Dim varSheetB As Variant     Dim iRow As Long     Dim iCol As Long        'Open C:\Example\Class 1_Lilly.xls     Set wbkA = Workbooks.Open(ThisWorkbook.Worksheets(1).Cells(1, 2))     Set varSheetA = wbkA.Worksheets("Class 1")         'Open C:\Example\Class 1_Lucy.xls     Set wbkB = Workbooks.Open(ThisWorkbook.Worksheets(1).Cells(2, 2))     Set varSheetB = wbkB.Worksheets("Class 1")        'Get the last row with data of worksheet A     a = varSheetA.UsedRange.Rows.Count     'Get the last column with data of worksheet A     b = varSheetA.UsedRange.Columns.Count     'Get the last row with data of worksheet B     c = varSheetB.UsedRange.Rows.Count     'Loop through all first row/colum to hight rows/columns that only exist in one sheet     For iRow = 2 To a       For jRow = 2 To c         If varSheetA.Cells(iRow, 1) = varSheetB.Cells(jRow, 1) Then           b2 = b + 1           varSheetA.Cells(iRow, b2) = "Y"           varSheetB.Cells(jRow, b2) = "Y"         End If       Next jRow     Next iRow         'Get larger number of rows     If a > c Then        d = a     Else        d = c     End If         'Locate and Highlight     For iRow = 2 To d       If varSheetA.Cells(iRow, b2) = "" And varSheetA.Cells(iRow, 1) > "" Then         For iCol = 1 To b          varSheetA.Cells(iRow, iCol).Interior.Color = RGB(0, 255, 0)         Next iCol       End If       If varSheetB.Cells(iRow, b2) = "" And varSheetB.Cells(iRow, 1) > "" Then         For iCol = 1 To b          varSheetB.Cells(iRow, iCol).Interior.Color = RGB(0, 255, 0)         Next iCol       End If     Next iRow               'Loop through all cells to highlight differences     For iRow = 2 To a       For jRow = 2 To c         If varSheetA.Cells(iRow, 1) = varSheetB.Cells(jRow, 1) Then             For iCol = 1 To b                 If varSheetA.Cells(iRow, iCol) <> varSheetB.Cells(jRow, iCol) Then                    varSheetA.Cells(iRow, iCol).Interior.Color = RGB(55, 242, 251)                    varSheetB.Cells(jRow, iCol).Interior.Color = RGB(55, 242, 251)                 End If             Next iCol         End If       Next jRow     Next iRow         'Close both Excel files and save changes     wbkA.Close SaveChanges:=True     wbkB.Close SaveChanges:=True End Sub

Following are the results:

Rows that only exist in one file will be highlighted in yellow and you can also use condition – Column H = “Y” – to filter the rows include in both Excel files.