Time and date related Conditional Formatting question in Excel

Please note that the very last question was added as an edit (i.e. early birds may have missed it).

As usual Teylyn's answers not only grat but with minute details !!!

To complement Teylyn's answer and to make the workbook active, as presume you need the showing of the time not only when workbook is opened but all along the workbook beeing active and even when opened and idle. I suggest the following:

1) You change your workbbok from simple no macro .xlsx to a macro .xlsm.
2) you create a Module in which you add the below code

 

Global RunWhen

Sub StartTimer()
    ' run it every 1min
    RunWhen = TimeSerial(Hour(Now), Minute(Now) + 1, 0)
    Application.OnTime EarliestTime:=RunWhen, Procedure:="Calculate", Schedule:=True
End Sub

Sub Calculate()
ActiveSheet.Calculate
StartTimer
End Sub

Select allOpen in new window

3) you add in your wokbook open the following code
---------------
Private Sub Workbook_Open()
StartTimer
End Sub
----------------

4) you add also in your sheet Worksheet_SelectionChange event the below code
----------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Calculate
End Sub
-----------------

5) Save exit the workbook and try it.

What this will do is refresh the workbook every minute and run Calculate as without it when the time changes pass 30min it will not change color unless you manually trigger calculate in the worksheet. if your worksheet is opened for say 2 hours and you do not do any calculation the time will show the old value whereas with this code even if the workshhet is inactive for as long as you want the time will indicate correctly. even is you simple move the cell selection it will update the time.

Hope this will be use\ful for you as feel you need the coloring (which is a very smart idea to be used in a clendar type where you would hv your appointment and somthing indicating the actual time !! that's how I see it.

PS for your easy refrence I have incorporated the code in Teylyn's attached file for you to see it better. For sure all the credit goes to Teylyn as my suggestion was not in the scoope of the question but felt it might be useful to you.

Enjoy
gowflow
TimeChange-Formating.xlsm

Just one small question, Steve. Do you expect B35 and C35 to ever be coloured yellow - or don't you care at that time of day?

regards, barry

...if you do want row 35 to be highlighted when the time is between midnight and 07:00 you can do this:

Make sure B35 = 0 not 1 as per teylyns setup. Select B35:C35 and apply this formula in conditional formatting

=$B1=MAX(IF($B$1:$B$35<=MOD($A$1,1),$B$1:$B$35))

format as required

See attached, A1 randomly selects a date/time in the next 24 hours - press F9 to re-generate so you can check how the formatting works - For "real" use A1 should revert to =NOW()

regards, barry
27380541.xlsx

Note: I believe there is a "bug" in Excel 2007 which affects conditional formatting based on formulas that would need to be "array-entered" if placed in a worksheet cell. When I re-open the workbook after closing I have to go into the conditional formatting before it works correctly (otherwise it seems to erroneously format 11:30 PM when it shouldn't). It shouldn't affect you if opened in Excel 2010 (although I can't check that right now).

If it's an issue then you can change to this formula

=$B1=MAX(INDEX(($B$1:$B$35<=MOD($A$1,1))*$B$1:$B$35,0))

regards, barry

>>barryhoudini:
...Do you expect B35 and C35 to ever be coloured yellow - or don't you care at that time of day?

The latter, Barry -- but your comments are always welcome and appreciated!

Thanks