How to Compare Two Excel Workbooks and Highlight Differences in Excel VBA

In this tutorial, I will explain how to compare two Excel Workbooks and Highlight Differences in Excel VBA macros.

Now suppose that we have two files as described below:-

File 01- “Compare Two Sheets_Old”

**Compare Two Excel Workbooks and Highlight Differences in Excel VBA**_Image_01

and File-02 “Compare Two Sheets_New”

Both workbooks have different worksheets such as “Jan-22” and “Feb-22”.

And we do not know if there are any differences between these two files with all the sheets.

Here in this example, we have two worksheets in both files but this VBA macro will work if you have multiple worksheets also.

And now we will write our VBA macro to find the differences between these two worksheets.

First of all, go to the Excel Ribbon and click on the Developer tab in Excel.

If you are not finding the ‘Developer’ tab in your Excel then use the below steps to get it.

Go to the File menu in Excel.

Click on ‘Option’ and you will get the below Excel Options dialogue box.

Click on “Customize Ribbon” and then go to the second box, as you can see in the image below.

See that there is a ‘Developer’ option with a check box, mark the tick on this check box, and click on the ‘Ok’ button at the bottom.

Once you press the “Ok’ button below, your ‘Developer’ tab will appear in the Excel Ribbon.

Now use the instruction below.

Compare Two Excel Workbooks and Highlight Differences

Go to the ‘Developer’ tab and select ‘Visual Basic’ from the listed options.

Click on ‘Visual Basic’ option and you will get the Visual Basic Application window.

In this window, go to the ‘Insert’ tab and click on ‘Module’ from the listed options.

See below the ‘Module’, where we will write our VBA code.

Use the below VBA code in this section.

If you want to copy this code, you can copy it from the below box.

Sub Compare_Two_Workbooks()

‘Declare the variable for both the workbooks

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws1 As Worksheet
Dim ws2 As Worksheet

‘Make sure both the files must be open

‘Set each of the 2 workbooks to object variables
Set wb1 = Workbooks(“Compare Two Sheets_Old.xlsm”)
Set wb2 = Workbooks(“Compare Two Sheets_New.xlsm”)

If wb1.Worksheets.Count = wb2.Worksheets.Count Then

    ‘Loop through worksheets
    For Each ws1 In wb1.Worksheets

      For Each ws2 In wb2.Worksheets

        If ws1.Name = ws2.Name Then

          ‘Compare the 2 worksheets
          For Each cell In ws1.Range(“A1”).CurrentRegion

            If cell.Value <> ws2.Range(cell.Address).Value Then

                cell.Interior.Color = vbYellow

            End If
          Next cell
        End If
      Next ws2
    Next ws1
   End If
End Sub

Compare Two Excel Workbooks and Highlight Differences_Image

Just have a look at this VBA code, and then execute this VBA code by pressing the ‘F5’ key from the keyboard shortcut to run the VBA macro.

Once you will press ‘F5’ or run the macro by the ‘Run’ button, and see that you will find all the differences between the workbooks of files with all the worksheets with the differences.