Excel 2007 : formulas with conditional formatting

**drj73** · 03-06-2012, 09:51 AM

hi there

very new to all this, so be gentle.

i know conditional formatting works slightly differently in 2007, hence me posting in here rather than in general questions.

i want to create a formula or format that changes the colour of the font depending on the result. for example
if A2 is greater than A1 then i want the text in the relevant cell to be blue, if A2 is less than A1 then i want it to be in red.
I can do this with conditional formating, but it doesnt take the formula as relative to the cell i apply it to, i have to write the formula again for the new location.
is there either a way to apply colours in an IF formula, or to copy the conditional formatting with a relative formula?

I hope that makes sense.

**NBVC** · 03-06-2012, 12:02 PM

Can you show an example of what you mean?

As long as you don't use $ in the references, then the references are relative and when you copy formats, then the references will be relative to position you copied to.

**drj73** · 03-07-2012, 08:00 AM

a.JPG

i am trying to get the colour of the text in column AW to change in relation to the figure in column AV.
if AV7 is greater than AW7 i want AW7 to have a blue font, if AV7 is less than AW7 i want AW7 to have a red font.

i have been doing this with Conditional Formatting and using the "use a formula to determine which cells to format" rule.
where i write the formula for greater or less than, and then apply a format to each answer.
The problem i have is i cant seem to then copy that format to other cells, as the formula will always relate to AW7 and AV7. it doesnt copy relative to the location i paste it.

i have tried taking the $ out of the formula and that didnt seem to help

**Pepe Le Mokko** · 03-07-2012, 08:07 AM

Please post a sheet, not an image - Thx

**drj73** · 03-07-2012, 09:09 AM

sorry, i have worked it out now, i was trying to apply the format to cells that it wasnt meant to be on, and that was confusing the results for the cells i did want.

NBVC solved it with the $ issue

cheers for looking