Part II: Working with Formulas and Functions
FIGURE 17.8
Configure the New Formatting Rule dialog box to apply the needed formula rule.
- In the list box at the top of the dialog box, click the option called Use a for-
mula to determine which cells to format. This selection evaluates values based on
a formula you specify. If a particular value evaluates to true, then the conditional
formatting is applied to that cell.
- In the formula input box, enter the formula shown here. Note we’re using the
COUNTIF function to evaluate whether the value in the target cell (E4) is found in
our comparison range ($B$4:$B$21). If the value is found, the COUNTIF function
will return a number greater than 0, thus triggering the conditional formatting. As
with standard formulas, you’ll need to ensure that you use absolute references so
that each value in your range is compared to the appropriate comparison cell.
=COUNTIF($B$4:$B$21,E4)>0
- Click the Format button and choose your desired formatting. This will open the
Format Cells dialog box, where you’ll have a full set of options for formatting the
font, border, and fill for your target cell.
- Click the OK button once you’ve completed choosing your formatting options.
- Click the OK button twice to confirm your formatting rule back on the New
Formatting Rule dialog box.
