Excel -- Conditional Formatting -- Based on another cell

Apply Conditional Formatting to a Row
You can apply conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For example, you could colour the entire row in a table, if the values in column B are over a set value.

1. Select the cells to be formatted (A2:D4 in this example)2. Choose Format>Conditional Formatting...
3. From the first drop-down list, choose Formula Is4. In the text box, enter a formula that refers to the active cell in the selection.
In this example, the formula is: =$B2>75
Use an absolute reference to column B ($B), to ensure that the conditional formatting in all columns refers to the value in column B. Otherwise, the formula will be adjusted in each column, and won't work properly.

5. Click the Format button.6. Select the formatting options, click OK7. Click OK

Excel -- Conditional Formatting -- Introduction

Conditional Formatting -- Introduction
What is Conditional Formatting?
Apply Conditional Formatting to a Cell
Conditional Formatting -- Based on another cell
Conditional Formatting -- Examples


What is Conditional Formatting?
Conditional formatting allows you to set rules for cell formatting. If the rules (conditions) are met, then the formatting is applied. You can have up to 3 rules in a cell.

For example, you can set conditional formatting so that a cell turns blue if it contains a value higher than 75 and turns green if it contains a value lower than 50.






Apply Conditional Formatting to a Cell

1. Select the cells to be formatted
2. Choose Format>Conditional Formatting...




3. Leave the first drop-down box set to Cell Value Is
4. In the second drop-down box, choose one of the operators. In this example, choose 'greater than'
5. In the text box, type a number or a cell reference. In this example, type the value you want to check -- 75.





6. Click the Format button
7. On the Patterns tab, select a colour for the conditional formatting -- blue, in this example.
You can also choose a Font format or a cell Border.
8. Click OK.





9. To add another conditional format, click the Add button.
10. Repeat steps 3 to 8, using the values and colours for the second conditional format.
11. Click OK, to return to the worksheet.




Conditional Formatting -- Introduction
Conditional Formatting -- Based on another cell
Conditional Formatting -- Examples
Conditional Formatting -- Documentation