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.

`SUM`

of the column or just a single cell value? – CharlieRB Aug 8 '12 at 11:33