Compare Two Columns in Excel and Highlight Matches

There are a few ways to compare a value in one column with the value in the other column.

The first way is by using an additional column that will show whether the value exists in the other one, and the second way is by using a simple VBA code.

Compare two columns and highlight matches (VLOOKUP)

Let’s say that we have two columns. One with a list of countries, and the second one with the countries we want to check whether they exist in the first column.

Now, we need another column where we can get the answer to whether the particular country from the first list exists in the second list.

Create a column between rows A and B and call it Formula. Now, type the following formula =NOT(ISNA(VLOOKUP(A2:A20,$C$2:$C$6,1,FALSE))) into cell B2. Use autofill for other rows.

You can use conditional formatting to make the values more distinguishable.

Compare two columns and highlight matches using VBA

The second method uses VBA code.

Let’s modify our example a bit by adding a different background to each cell in the second column and changing the text color to white.

In order to insert the VBA code, you need to open the VBA Editor by pressing Alt + F11.

Enter the following code into VBA Editor.

Now press the run button (F5).

This will set the same color and background if there is a matching cell.

Code explanation

2. We declare two ranges: aRng and bRng.
3. The first range with the list of countries.
4. The second range with the list of countries.
6 and 7. Check each cell with one range with each cell in the second range.
8. If the cell is Nothing then do nothing.
10 and 11. Otherwise set the background and font color in a cell in the first row with the same as the matching cell in the second column.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba