Compare Two Worksheets Excel VBA Macro

In this macro example, we compare two worksheets with data from two different versions of the same data set. Thus, we can see if there are any changes in version 2 compared to version 1. The changes are highlighted in yellow.

Macro code:

 

  Sub CompareTwoWorksheets()

       Dim dataRng As Range, selCell As Range

 

      Worksheets(1).Activate

 

      Set dataRng = ActiveCell.CurrentRegion 'whole data set range

 

      'Start loop to compare cell to cell

      For Each selCell In dataRng

          If selCell.Value <> Worksheets(2).Range(selCell.Address).Value Then

              Worksheets(2).Range(selCell.Address).Interior.Color = vbYellow

          End If

      Next selCell

  End Sub

 

Macro explained:

• First we declare two range object variables (dataRng and sellCell). The variable dataRng represents the whole range in scope, and selCell represents each cell within that range.
• We select the first worksheet (one of the versions, in this case, version 1), and set the dataRng to the whole data set range using the CurrentRegion property. Whatever cell or range is selected, the range of all cells around will be represented with CurrentRegion.
• Then we start to loop through all cells in that range with a For Each loop. Within the loop, we use an IF statement to compare the value of each cell between the two worksheets. The Address property is used to get the same cell in the second worksheet.
• Finally, if the value of the cells is different, we highlight that cell in the second worksheet in yellow, changing the interior color of the cell.

This is how we compare two worksheets in Excel using VBA macros.

Other examples:

• How to Send Email From Excel with Excel VBA macros
  
• How to Add Unique Entries Validation List with Excel VBA macros
  
• How to Separate Sheets Into Workbooks with Excel VBA macros