• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change cell color based on time

I

IKHAN

Member
Hi,

Cell E1 date / time extracted from another sub routine in format (m/d/yyyy h:mm AM/PM). require help on date / time background cell color to change.

Not getting desired result with conditional formatting, Kindly suggest.

E1 - Default background color - green

E1 time less than 15 minutes - change background color to yellow
E1 time => than 1 minute - change background color to Red

conditional formats used :

=NOW()-INT(NOW())>E1+TIME(0,1,0)

=NOW()-INT(NOW())>E1-TIME(0,15,0)
 
Not sure how to edit my previous post , Reposting with more clarification

Not getting desired result with conditional formatting , With below formula color changes if only cell is clicked.

=NOW()-INT(NOW())>E1+TIME(0,1,0)

=NOW()-INT(NOW())>E1-TIME(0,15,0)

If anyone can provide vba with below requiements..that would be awesome..

Format of E1 cell (m/d/yyyy h:mm AM/PM)

E1 cell - Default background color - green
E1 cell time less than 15 minutes - change background color to yellow
E1 cell time equal to Now() system time - change background color to Red

Reset to back ground green If new time is entered.

Note : Date\Time is being captured in cell E1 thru another sub routine
and E1 cell is on sheet2 of my workbook.

Thanks in advance ...
 
Last edited:
Hi ,

I have tried the following :

1. Make the default colour GREEN.

2. For YELLOW , use the following :

=AND(NOW() < $E$1, (NOW() >= $E$1 - TIMEVALUE("00:15:00")))

3. For RED , use the following :

=AND(NOW() < $E$1, (NOW() >= $E$1 - TIMEVALUE("00:01:00")))

The order of these two rules is important ; the rule in (3) above should be the first rule , at the top , and the rule in (2) should be the second rule , at the bottom of the list of rules.

However , the main point to note is that CF does not change automatically ; if the cell is coloured GREEN , it is not going to change to YELLOW and then RED on its own ; the changes will take place only when recalculation is done , and this will happen only when any user-driven data entry is done or when the worksheet is manually recalculated by pressing the F9 key or the key combination CTRL ALT F9.

Narayan
 
You must log in or register to reply here.
Back
Top