PDA

View Full Version : Alter colour of cell text based on value of cell next to it?



lad
10-10-2007, 05:12 AM
Hi,

I have a number of values running down column C. Rather than applying conditional formatting to the column to change the text colour of these values what I would like to do is change the colour of text in the cell next to each value (ie. Column D).

I can do this for an individual cell using a formula in conditional formatting which appears something like =$C$4<5 and would apply to $D$4 but because these aren't relative I can't copy this formula down for each cell in Column D. Make sense?

Is there a way I can do this using some other method?

Cheers.

p45cal
10-10-2007, 05:46 AM
change the formula to
=$C4<5
then use the format painter to paint the conditional format to the rest of column D. The reference to column C is absolute, but the relative reference of the row number allows the conditional formatting to be painted down successfully.

lad
10-10-2007, 07:22 AM
Hi, I've tried that but I can only 'format paint' one cell at a time. If I select more than one cell say D4 to D10 the formatting of cells $D$4:$D$10 relate to $C4 and not each cell in series.

Cheers.

mdmackillop
10-10-2007, 10:53 AM
You can format paint the whole column by applying it to the D heading. You can also select the formatted cell and drag using the fill handle to apply to other cells.

lad
10-10-2007, 12:59 PM
Thanks. Sorted. Is there a way to apply a formula based on whether the cell is empty. In other words, if cell Cx is empty apply a particular format to cell Dx ?

mdmackillop
10-10-2007, 02:09 PM
You need two conditions
=AND($C1<5,$C1<>"")
=$C1=""