Excel 2010: Colour cells in a column based on values in another column

I need to do the following in Excel 2010

Colour a column based on another columns value

For example,

Status      Balance
Green       0
Red         25
Red         60
Green       0

The 'Balance' is populated using SUM(). I could do it for a single cell, ie., for the first row. How can I set the rules for the rest of records ?

Solution 1:

(I have only a german version available but I will try to explain everything with screenshots)

1. Mark all Cells that should get the colour
2. Activate "Start" tab
3. Click on that button:
4. Click on "New Rule"
5. Select the last option and enter your formula:
6. set up your formatting and klick OK.

My example shows how to colour all selected cells green when A1 contains "true" without quotes. You can change that formula to your needs. Make sure your formula starts with "=".

Solution 2:

If you want to base the color off another cell's value, then you should use an absolute reference or the INDIRECT formula.

Normally, we might want to color the cell itself (e.g. if the value is greater than 20). We could use a formula =A1>20. This is a relative reference: if you applied it to several columns, then it would treat each cell as A1 and do the comparison.

Suppose that we want to color an entire row if a value in Column B is greater than 20. In that case, we'd select the appropriate columns to apply the conditional formatting and use the formula =$B1>20. The $B means it only looks at the value in Column B. The 1 is still relative, so it will look at each row's value in Column B.

If you wanted to do something a bit more complicated, like color a row based off a value two rows higher, then you'll need to use INDIRECT and ROW in your formula.