Comparing columns of data manually is not a realistic approach when working with large data sets. Let us show you how to compare two columns in Excel using VBA and automate this process.
How to Compare two Columns in Excel
VBA has a built in function called StrComp, which can compare two separate strings. This function returns an integer based on the result of the comparison. Zero ‘0′ means a perfect match, and the sample code we give 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. Note that the third argument is optional. If not omitted, the Option Compare setting will determine the type of comparison. Below are 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 the user to select columns in a single range. Selected range is assigned to a range variable bothcolumns, and each cell in that range is compared row by row. With statement allows us to not use bothcolumns every time, and the For…Next loop is how we can check for every cell.
After you get the range, you can use the ColorIndex property to set that range a color. Below 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 to be able to use the code. 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.
Sub HighlightColumnDifferences() Dim bothcolumns As Range, i As Integer Set bothcolumns = Selection With bothcolumns For i = 1 To .Rows.Count If Not StrComp(.Cells(i, 1), .Cells(i, 2), vbBinaryCompare) = 0 Then Range(.Cells(i, 1), .Cells(i, 2)).Interior.ColorIndex = 6 End If Next i End With End Sub