Microsoft Office Tutorials and References
In Depth Information
Comparing Two Ranges by Using Conditional Formatting
Figure 164-3 shows the result. Names that are in the old list but not in the new list are
highlighted. In addition, Names in the new list that aren’t in the old list are highlighted in a different
color. Names that are not highlighted appear in both lists.
Figure 164-3: Conditional formatting causes differences in the two lists to be highlighted.
Both of these conditional formatting formulas use the COUNTIF function. This function counts the
number of times a particular value appears in a range. If the formula returns 0, it means that the
item doesn’t appear in the range. Therefore, the conditional formatting kicks in and the cell’s
background color is changed.
The cell reference in the COUNTIF function should always be the upper left cell of the