Highlight matches and differences in each row
To compare two columns and Excel and highlight cells in column A that have identical entries in column B in the same row, do the following:
Select the cells you want to highlight (you can select cells within one column or in several columns if you want to highlight entire rows).
Click Conditional formatting > New Rule… > Use a formula to determine which cells to format.
Create a rule with a simple formula like =B2=A2 (assuming that row 2 is the first row with data, not including the column header).
To highlight differences between column A and B, create a rule with the formula =B2<>A2
If you are new to Excel conditional formatting, please see How to create a formula-based conditional formatting rule for step-by-step instructions.
Highlight unique entries in each list
Whenever you are comparing two lists in Excel, there are 3 item types that you can highlight:
Items that are only in the 1st list (unique)
Items that are only in the 2nd list (unique)
Items that are in both lists (duplicates) - demonstrated in the next example.
This example demonstrates how to highlight items that are in one list only.
Supposing your List 1 is in column A (A2:A6) and List 2 in column C (C2:C5). You create the conditional formatting rules with the following formulas:
Highlight unique values in List 1 (column A): =COUNTIF(C2:C5, A2)=0
Highlight unique values in List 2 (column C): =COUNTIF(A2:A6, C2)=0
Let me know if this helps.