How to compare two rows in Excel using VBA

by | Oct 10, 2018 | Excel Macros, Excel Tips & Tricks

How to highlight differences between rows with VBA

Comparing data row by row manually can be a tedious process if done manually. Let us show you how to compare two rows in Excel using VBA and automate this process.

 

How to compare two rows in Excel using VBA

VBA has a built in function called StrComp that can compare two strings. This function returns an integer based on the result of the comparison. Zero '0' means a perfect match, and the code example below highlights the cell if the result is NOT equal to '0'.

StrComp function gets 3 arguments: First 2 arguments are the strings to be compared and the last one is the comparison type (optional). If omitted, the Option Compare setting determines the type of comparison. Below are all available options:

  • vbUseCompareOption: Performs a comparison using the setting of the Option Compare statement.
  • vbBinaryCompare: Performs a binary comparison. Case sensitive.
  • vbTextCompare: Performs a textual comparison. Not case sensitive.
  • vbDatabaseCompare: Performs a comparison based on information in your database. Microsoft Access only.

The code requires user to select the rows in a single range. Selected range is assigned to a range variable bothrows and each cell in that range is compared column by column. Using a With statement, we don't have to repeat bothrows for each row, and For…Next loop is how we can check every cell.

 

Highlighting

After you get the range, you can use ColorIndex property to set that range a color. Here are some index numbers for colors:

  • 3: Red
  • 5: Blue
  • 6: Yellow
  • 0: No Fill

First, you need to add the module into the workbook or the add-in file. Copy and paste the code into the module to run it. The main advantage of the module method is that it allows saving the code in the file, so that it can be used again later. Furthermore, the subroutines in modules can be used by icons in the menu ribbons or keyboard shortcuts. Remember to save your file in either XLSM or XLAM format to save your VBA code.

 

Sample code for highlighting

Sub HighlightRowDifferences()

    Dim bothrows  As Range, i As Integer

    Set bothrows = Selection

    With bothrows

        For i = 1 To .Columns.Count

            If Not StrComp(.Cells(1, i), .Cells(2, i), vbBinaryCompare) = 0 Then

                Range(.Cells(1, i), .Cells(2, i)).Interior.ColorIndex = 6

            End If

        Next i

    End With

End Sub