One of the most frequent requests I receive from readers is how to highlight list values when they change. Conditional formatting is the answer, however, even though you might try the right formula, if you don’t start at the right cell, it won’t work.
The figure below shows a list of text values. Now suppose that want to highlight a cell when the value above that cell is different. Specifically, the first item is Actuaries and it occurs twice. The value changes to Actuary in cell A4, so you’d want to highlight cell A4.
You might consider a simple formula such as =A3<>A2 using the conditional formatting feature, but as I mentioned, positioning is everything in this solution. Let’s illustrate what might happen:
- Select A2:A35.
- Click the Home tab (if necessary). In Excel 2003, choose Conditional Formatting from the Format menu and skip to step 4.
- In the Styles group, choose New Rule from the Conditional Formatting dropdown.
- In the top pane, choose the Use A Formula To Determine Which Cells To Format option. In Excel 2003, choose Formula Is from the Condition 1 dropdown.
- Enter the following formula: =A3<>A2
- Click the Format button.
- On the Fill tab, choose a color. In Excel 2003, click the Patterns tab.
- Click OK twice. As you can see, the formula doesn’t work.
Viewing the results of the formula in a couple of helper columns provides insight we need. Our selection (step 1) was incorrect.
Let’s use the simple formula with a different selection. Repeat the steps above, but this time, select A3:A35.
Thanks to Steve Erbach for submitting this easy solution!