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.
Dim aRng, bRng As Range
Set aRng = Range("A2:A20")
Set bRng = Range("B2:B6")
For Each aCell In aRng
For Each bCell In bRng
If aCell Is Nothing Or bCell Is Nothing Then
ElseIf aCell.Text = bCell.Text Then
aCell.Font.Color = bCell.Font.Color
aCell.Interior.Color = bCell.Interior.Color
Now press the run button (F5).
This will set the same color and background if there is a matching cell.
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.