Change cell color based on date

[mrkris1982]

Wondering how I could code the cell to change to a different color for 5 different scenariois:

Blank (no color) if no text is in the cell
Bright Green if text is in the cell for 0-24 hours (restart time if new text is added to existing text)
Yellow if text has been unchanged for 24-48 hours
Rose if text has been unchanged for 72 hours
Red if text has been unchanged for 96 hours

Any ideas?

 

[bobsan42]

ideas - yes but first U must decide how you will track when a cell value is changed.
you probably need some vba Worksheet_Change events.
suggestion - create a hidden sheet with name corresponding to the one in question.
when the event Worksheet_Change appears record the event time in the hidden sheet in the location of the changed cell.
then a bit of conditional formatting shoul do the rest.
P.S.: you have 4 required conditions - Excel prior 2007 supports only 3.

 

[doofusboy]

Are you using Excel2007 and have you looked into conditional formatting?

 

[mrkris1982]

I am using Office 2003. I know I can use CF, but only three options.

I could make it only 3 conditions (<24 hrs, <36 hours & >36 hours) but I do not have any idea how to code this formula in CF

 

[bobsan42]

if you know when the cell is changed and you call this moment TimeChanged then:
(<24 hours): now()-TimeChanged<1
(<36 hours): 1<=now()-TimeChanged<1.5
(>36 hours): now()-TimeChanged=>1.5

 

[mrkris1982]

Cool;

So I just need to go to CF, choose FORMULA IS and paste your formulas below, correct?

 

[mrkris1982]

There must be something more to it than just puttin it in CF because I tried it, then enterred text and the cell did not change colors.

 

[bobsan42]

not exactly - first you must decide how you will assign a value to TimeChanged
and this value will be different for each cell - it must be automatically recorded somewhere on each change - some vba must be written - but i don't have time right now
maybe later or tomorrow