Thread: Highlight a date cell 3 weeks in advance

See your file attached.

Conditional format # 1 is =AND($B$2-$A$2<=0,$B$2-$A$2>=-21)
Conditional format # 2 is =$B$2-$A$2>0

Dion

I was going to say:

Rule 1 (red): =A2<B2
Rule 2 (green): A2-B2<=21

Wow i am impressed and such a speedy response too, tank you so much i can now smile again

You're welcome. Thanks for the "scales tap".

i AM SUCH AN IDIOT i am so sorry i forgot a ref cell
ok lets start again
I have a delivery to customer date 30-apr-11 (cell a1)
21days before the date in cell a1 cell b2 should turn GREEN
when the date in cell a1 has expired cell b2 should turn RED

again please see attached

please forgive my earlier mistake

I'm doing this right before going to bed so I hope it's right:

sorry for the l8 reply
thank your so much for your help i am doing quite a big spread sheet so more questions to come i am sure

You're welcome. Thanks for the "scales tap".

Originally Posted by Cutter

You're welcome. Thanks for the "scales tap".

Hi.
I found it quite interesting and can be used on some of my worksheets. however, one question to it is: how to apply the condition formatting in the entire column?

Not sure why you would quote my thank you to the OP and I can't respond to your request because you aren't following the forum rules.

Click on the Forum Rules link at top of page and pay particular attention to Rule #2.