turn a cel a color based o todays date



Projected dates left to right drom D5:N5

Actual dates are entered one at a time below projected date as a project
ends in D6:N6

The answer goes in D7:N7

All works fine.

1. If Todays date exceeds Projected date =>1 day
ie: Expected 2/11/09 and Today is 2/12/09

2. I'd like to turn "That Cell" in D6:N6 Blue, (not all cells,) just the
Cell that exceeds the date based on Today. =>1

3. When a date IS entered into a BLUE D6 rows cell, I would like the cell to
look normal, turn the BLUE OFF and show the date entered.

Can this be done?




Select D6:N6 and enter this formula in CF formula field, choose blue pattern!
If you enter a new date in D6 that is <=TODAY(), blue will be switched off.
If you want to switch off blue at ANY new date then it can be done only via a
VBA event sub.



„Mark†ezt írta:

Bernard Liengme

You could select all the cells that need this conditional formatting and in
the conditional formatting dialog use: Formula Is =D6-TODAY()>=1 and set
required format
I have used D6 here as I assume this in the first cell in the selected range
However, is any cell has a value grater than about 39800 then it could be
mistaken for a date
best wishes


Thanks Stefi,

I will give it a try,

Don't understand the VBA staement, I'm a newbie!

Can this CF Be included (added to) under a full sheet that has formulas,
without changeing current text/date /headings.

Or does it appear, I just start his new CF at the next new line?


No, I entered a test date which poulated D5:N5

D6 now is 4/25/09 and dates gets higher to the right)
However D6:N6 are all blue.

I would like the cells to remain plain and only to turn BLUE, if the
coorsponding date above D6:N6, exceeds the date above in D5:N5 =>1 from
todays date

I'm sure I didn't say it right.

Thank you for your quick response.




I covered D6:N6

enterd cf

Populated dates across D5:N5

D5 = 2/7/09 Being that today is 2/9/09
D6 did not change to blue

Any ideas?

Thank you for oferring your time and expertise



Bernard Liengme

Did you enter =D6-TODAY()>=1
in the conditional formatting cell?
Send me a file (remove TRUENORTH.)
best wishes

Shane Devenshire


Why don't you show us some sample data and explain with that what you want
to have happen?

You say the "answer" goes into D7:N7, the answer to what, and what does that
have to do with you process.

You have and Expected Date and a Projected Date what is the difference and
where are they located?

A final point, eventually, when its all clear your formula will look
something like

You should be highlighting row 6 when you do this.




Thanks Shane.. My superfulous explainations, are because I feel I'm
describing something for a blind person, because I am an amatuer.

When ..If I just state the obvious, usually, I get exactly what I need.
It did turn out to be( for me ) a set of advanced CF's

Bernard, hit them SPOT on.


And I Thank you for taking your time to deal with guys like me..

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question