Conditional Formatting

[Guest]

Hello Excel(lent) Colleagues,

I am looking for the following way of conditional formatting.

I have a row in which field
A1 contains a text.
B1 contains a date
C1 contains the date of B1+7 days
D1 contains the date of C1+7 days
E1 contains the date of D1+7 days

I want to mark the text in A1 with a particular colour if any of the date
fields is marked with an X.

I tried to work with:
Formula is : =IF($C$3:$H$3="X";TRUE;FALSE)

The effect of this is that only if you cross B1 it will turn a different
colour, but if you move that cross from B1 to say E1, the colour dissapears,
were it should stay.

Please help me !!

 

[Roger Govier]

Hi jay

Try
=COUNTIF(C1:E1,"x")>0

Regards

Roger Govier

 

[Stephen]

Hello Excel(lent) Colleagues,

I am looking for the following way of conditional formatting.

I have a row in which field
A1 contains a text.
B1 contains a date
C1 contains the date of B1+7 days
D1 contains the date of C1+7 days
E1 contains the date of D1+7 days

I want to mark the text in A1 with a particular colour if any of the date
fields is marked with an X.

I tried to work with:
Formula is : =IF($C$3:$H$3="X";TRUE;FALSE)

The effect of this is that only if you cross B1 it will turn a different
colour, but if you move that cross from B1 to say E1, the colour
dissapears,
were it should stay.

Please help me !!

Your post doesn't make sense. You describe what is in columns A to E of row
1 but then quote a formula referring to columns C to H of row 3.

Anyway, you cannot use an array formula in conditional formatting. To make a
cell change colour when any of the cells $C$3 to $H$3 contain "X", you could
use this formula:
=ISNUMBER(MATCH("X",$C$3:$H$3))