Conditional format for date

[George Gee]

Hi All

Conditional formatting problem:

Example
A B
1 24/Sept/04 £300.00
2 25/Sept/04 £250.00
3 27/Sept/04 £275.00
4 28/Sept/04 £420.00

This is what I would like to achieve:

If the date in Column A is current, then conditionally format
the adjacent cell in Column B: eg: =A1=TODAY() (and copy down).

But I wish this condition to apply until the next date in the row
below is current, then lose the formatted condition.

To put it another way:

On the 25/Sept/04 & 26/Sept/04 cell B2 is conditionally formatted.
When the 27/Sept/04 is reached, then cell B3 is conditionally formatted,
and cell B2 is not!

Can this be achieved?

Many thanks.

 

[Frank Kabel]

Hi
try the following:
- select B1:B20
- goto the conditional format dialog and enter the following formula:
=ROW($B1)=MATCH(TODAY(),$A$1:$A$10,1)
- choose your format

 

[Frank Kabel]

Hi
do you want to color the entired row or column?

 

[George Gee]

Hi Frank

No, just the corresponding cell in Column F.
Many thanks
George Gee

*Frank Kabel* has posted this message:

 

[Frank Kabel]

Hi
then just pplay my first instructions:
- select F4:F20
- goto the conditional format dialog and enter the following formula:
=ROW($F4)=MATCH(TODAY(),$A$4:$A$100,1)
- choose your format

 

[George Gee]

Hi Frank

Got it!
Thanks very much.
I was not changing the Column from "B" to "F".
Don't think my head is the right shape for Excel!

Again many thanks, you're a genius!

George

*Frank Kabel* has posted this message:

 

[George Gee]

Hi Frank.

Thanks for the response, the formula you gave me
works great using a mock-up worksheet.

However: in the Worksheet I wish to use,
the dates are in Column A, starting on row 4,
the data I wish to conditionally format
is in Column F, and I wish to format the entire column,
how do I change the formula to give this result?

Many thanks for helping me thus far!

George Gee



*Frank Kabel* has posted this message: