Using Formulas in Conditional Formatting

[ozgeek]

In Excel 2007 I have a spreadsheet for our companies phone billing, I want to use conditional formatting to highlight who is going over their mobile plan usage.
(With colour getting more dramatic the more they go over )
So it is basically =F2-E2>20 turn font red, if F2-E2>50 turn fill red, and so on.
This part is not my problem.

The real problem occurs when I try to apply these formatting to the entire column. I have tried the format painter and the Paste Special>Formats with the same result.
The formula stay exactly the same as the original cell so in cell F50 it will still have the formula =F2-E2>20
and a strange side occurance is that all the cells where the formatting has been applied to, if you change the formula in one cells all of them change.

I hope I'm missing something simple and that someone is able to explain what is going wrong

Thank you
Dianne

 

[GlennUK]

Did you happen to have absolute references for the cells, like:
=$F$2-$E$2>20
?

If so, remove the $ signs.

 

[ozgeek]

Glenn you are a legend, it works perfectly now.
Thank you you have saved me so much work

 

[GlennUK]

Glad to help!