Alter colour of cell text based on value of cell next to it?

[lad]

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]

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]

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]

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]

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]

You need two conditions
=AND($C1<5,$C1<>"")
=$C1=""