Conditional formatting for different rows based on a cell reference.

 
In this tip, I will show you how to apply “Conditional formatting” on multiple rows based on a cell reference.

As an example, I’ve created a table with several columns and a cell H1 (1) where the condition to be met is specified.

First, select the entire table with the exception of the column titles.
It is important that the cell A2 is selected. It is, in fact, at the bottom and to the right in the selected and copied range .(A)

Then click the “Conditional Formatting” button in the “Ribbon” (2) 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” (4) option.
Then places the cursor in the text box of the dialog box and click the cell B2 (5).
This is namely, the cell in the row, the contents of which must satisfy the condition in cell H2.

This places the following formula in the textbox: = $ B $ 2 (6).

Make this absolute cell reference, semi-absolute by pressing the F4 key on your keyboard twice (7).
This makes the column reference, absolute and the row reference, relative.
This formula is in fact, copied to the underlying rows and adjacent columns, so the column letter remains the same but, the row number needs to be adjusted.
Then we have this.

Type the equal sign (=) in the text box and select the cell with the condition (8).
In this case, it is cell H2.
This makes this cell reference an absolute cell reference in the formula (9).
And that’s good, because the formula is copied to all rows that are selected in the worksheet and, the cell where the condition is given, must always remain the same.

Click the “Format” button.

In the “Format Cells” dialog box, give a format for the cells according to your choice.
I select an orange background but, you can select what you want.
Click the OK button.

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

As you can see all the rows that satisfy the condition, are conditionally formatted.
If you change the date in cell H2, then the rows that satisfy this condition will automatically be changed.

Featured Courses

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