Conditional formatting for different rows on the basis of the cell contents of a cell in the row.

 
In the previous tip, I showed you how to format an entire row based on a cell reference.

In this tip, I will show you the same thing but, only on the basis of the cell contents of a cell in the row.

As an example, I have made a column (1) that shows whether the invoice is paid or not.
The idea is to color the entire row when “no” is the value.

First, select the entire table with the exception of the column headings (A).
It is important that here also, the cell A2 is selected.
Then click the “Conditional Formatting” (2) button in the “Ribbon” and select “New Rule” (3) from the drop-down menu.

In the dialog box that appears, select the “Use a formula to determine which cells are formatted” option(4).
Then place the cursor in the text box of the dialog box and click the cell H2 (5).
This places the following formula in the space provided: H = $ $ 2 (6).

Press the F4 key on your keyboard twice, to make this absolute cell reference a semi-absolute one. So, type = $ H2
Type the equal sign (=) in the text box and type the following condition that must be met, in quotes. In this case, it is “no” (7).

Click the “Format” (8) button.

In the “Format Cells” dialog box, specify a format of your choice, for the cells.
I have selected a light red background but, you can do what you want.
Click the OK button (9).

Click the “OK” button again, to close the “New Formatting Rule” dialog box.

As you can see, the defaulters are shown in a light red background.

Featured Courses

• Downloading iPad Apps
• Lesson 1: Introduction to Publisher
• Lesson 65: File Formats