Conditional Formatting based on another cell

In Excel 2003 you can apply conditional formatting that checks the value in one cell, and applies formatting to other cells, based on that value. For instance, let's say you need to change font colour of the entire column C in a table, if the values in column B are over a specific value.

How to apply conditional formatting to a column based on another column

  1. Select the cells to be formatted (Column C in our example)
  2. Choose Format > Conditional Formatting
  3. Now from the first drop-down list, choose "Formula Is"
  4. In the text box, enter a formula that refers to the column B. In our example, the formula is: =B1>100 for cell C1. Obviously, for cell C2 it will be =B2>100.
  5. You can use an absolute reference to column B by using a dollar symbol $B, to ensure that the conditional formatting in all columns refers to the value in column B.
  6. Now it is time to format the output - click the Format button.
  7. Select the formatting options, click OK
  8. Click OK

If you want to you can simply copy the conditional formatting from cell B1 to cells below.