In this blog post I will demonstrate a conditional formatting formula that will highlight common records in two lists. The image above shows you highlighted records in List 1 that also exists in sheet2.
$A2 is a cell reference to values in column A. The column ($A) is absolute but the row (2) is relative. When the conditional formatting formula executes the next row, only the row number changes, example $A3. The named ranges do not change.
If the COUNTIFS function finds one matching record it returns 1, if nothing is found 0 (zero) is returned. 0 (zero) is the equivalent to FALSE so the Conditional formatting will not highlight a cell i FALSE is returned.
Highlight common records from two lists Excel 2007
How to apply conditional formatting formula:
Select cells A2:C11 (Sheet: List 1)
Click "Home" tab
Click "Conditional Formatting" button
Click "New Rule.."
Click "Use a formula to determine which cells to format"
Type =COUNTIFS(YEAR, $A2, ASSET, $B2, COST, $C2) in "Format values where this formula is TRUE" window.
(The formula shown in the image above is not used in this article)
Click "Format.." button
Click "Fill" tab
Select a color for highlighting cells.
Highlight common records from two lists Excel 2003