Conditional formatting for time of day in Excel

not an easy one but you'll have to use the date codes. dates/times are stored as a number and converted to the appropriate date/time.

http://www.mvps.org/dmcritchie/excel/datetime.htm
explains in details, but the time is actually after the decimal place and is a fraction of the day.
if you can set your conditional formatting based on the decimals only you should be ok.

each hour is 1/24=0.0416666
each 15 mins is 0.0416/4=0.010416665

The attached should do the trick.  I am clearing the fill for all cells each time, but to revert to the previous fill color, I would store away that color somewhere and retrieve it when it's time to move to the next cell.  If that's an essential part of the functionality, let me know.

Good luck,

sdwalker
MyTimer.xlsm

BTW - I missed the whole "Conditional Formatting" thing.  If it can't be done in code, then ignore my post.  Sorry.

Tommy missed u by a whisker .... :)

Thanks for the responses.

>>conditional formatting on its own won't necessarily be enough - If one cell is formatted then it won't change to another unless the workbook re-calculates - otherwise you need some VBA to re-check the time

If possible, could you post the VBA code for updating/recalculating the time?  Also does it result in a continuous or "real time" display of the time or does the code have to specify the frequency of the updates?  If the latter, then updating every 60 seconds is ample.

Oh, one other thing:  If you can post the code, do you mind doing it in a way that it simply shows the current time in some separate cell (ie not mixed in with the formulas for the column mentioned above)?  If I've got a "clock" cell somewhere showing the current time and updating every minute then I can handle the conditional yellow formating (by just comparing to the clock).  Also, I've got other Excel files in which a clock cell would be helpful.

Thanks

Add a module and dump this code
-------
Option Explicit

Public Sub DoTick()
Application.Calculate
Application.OnTime Now() + TimeValue("00:01"), DoTick()
End Sub
--------
On your workbook_open event add this code
DoTick

To show the current time change the following
---
Option Explicit

Public Sub DoTick()
Application.Calculate
Application.OnTime Now() + TimeValue("00:01"), DoTick()
End Sub
----

to

----
Option Explicit

Public Sub DoTick()
Application.range("A1").value=now()
Application.OnTime Now() + TimeValue("00:01"), DoTick()
End Sub
-----

By the way, remember to format the [A1] cell as "h:mm:ss AM/PM;@" to see the clock in action.

If you use OnTime, you should store the scheduled time in a public variable so that you can cancel it when you close the workbook. Otherwise, the workbook will get reopened unless you exit Excel completely.

Rorya is correct. In order to be clean, shut the event cleanly by setting the optional Schedule argument to False. Here is an article that tell you how to do it the right way. (See towards the end of the article).
http://www.ozgrid.com/Excel/run-macro-on-time.htm

Thanks for the responses.

>>Add a module and dump this code

This has me confused because I looked for something to "dump" which to me means "delete" (as in:

"I d___ed the trash in the bin so
it can be taken to the d___
in a d___ truck, etc...

...etc.) but I could not find anything to delete.  However, now I'm wondering if "dump" is programming jargon for "copy/paste" and you want me to paste the code into a module.  Please clarify.


>>On your workbook_open event add this code
DoTick

Can you provide more detail for this instruction also?  In the Excel documentaion, I found a section called, "Run a macro:Create a VBA procedure for the Open event of a workbook."  Is that what you are referring to?  I assumed it was so after setting Macro Security to, "Enable all macros...", I followed these steps:

-----
# In the Project Explorer window, right-click the ThisWorkbook object, and then click View Code.
# Add the following lines of code to the procedure:

Private Sub Workbook_Open()
    MsgBox Date
    Worksheets("Sheet1").Range("A1").Value = Date
End Sub

# Switch to Excel and save the workbook as a macro-enabled workbook (.xlsm).
#Close and reopen the workbook. When you open the file again, Excel runs the Workbook_Open procedure, which displays today's date in a message box. (Note that cell A1 on Sheet1 also contains the date as a result of running the Workbook_Open procedure.)
-----

That seemed to work OK because I got the msg box and date in A1.  So next, I removed:

    MsgBox Date
    Worksheets("Sheet1").Range("A1").Value = Date

from the code and replaced it with:

    DoTick

so it is:

Private Sub Workbook_Open()
    DoTick
End Sub

which is what I was hoping you wanted me to do.  I also "pasted" this in a module:

Option Explicit

Public Sub DoTick()
Application.range("A1").value=now()
Application.OnTime Now() + TimeValue("00:01"), DoTick()
End Sub

but it always gives errors and even after trying a few things my feeble brain thought might help, the errors persist, e.g.:

Compile error:
    Expected Function or variable
    [DoTick () highlighted]

Compile error:
    Syntax error
    [DoTick turns red]


I'm pretty much clueless on this so if you could get me pointed in the right direction, I would appreciate it.

Thanks.

>>Here is an article that tell you how to do it the right way. (See towards the end of the article).
http://www.ozgrid.com/Excel/run-macro-on-time.htm 


By the way, I read through the link and followed the steps shown below but in this case, I got an error saying:

    Run-time error '1004':

    Method 'OnTime' of object'_Application' failed

Maybe it's just something I should wait and try again tomorrow.  :)


From the link:

-----
Let's now suppose you want to run this macro (MyMacro) at 15 minute intervals after opening your Workbook. Again we will kick it off as soon as the Workbook Opens so right click on the Excel icon next to "File" and select "View Code".  In here put;

Private Sub Workbook_BeforeClose(Cancel As Boolean)

     Application.OnTime dTime, "MyMacro", , False

End Sub

Private Sub Workbook_Open()

  Application.OnTime Now + TimeValue("00:15:00"), "MyMacro"

End Sub
Now in any Standard Module (Insert>Module) place this;

Public dTime As DateSub MyMacro()dTime = Now + TimeValue("00:15:00")Application.OnTime dTime, "MyMacro"

'YOUR CODEEnd Sub
Note how we pass the time of 15 minutes to the Public Variable dTime This is so we can have the OnTime Method cancelled in the Workbook_BeforeClose Event by setting the optional Schedule  argument set to False. If we didn't pass the time to a variable Excel would not know which OnTime Method to cancel as Now + TimeValue("00:15:00")is NOT static, but is when passed to a variable. If we didn't set the optional Schedule  argument set to False the Workbook would automatically open every 15 minutes after closing it and run MyMacro

Let's now suppose you want to run this macro (MyMacro) at 15 minute intervals after opening your Workbook. Again we will kick it off as soon as the Workbook Opens so right click on the Excel icon next to "File" and select "View Code".  In here put;

Private Sub Workbook_BeforeClose(Cancel As Boolean)

     Application.OnTime dTime, "MyMacro", , False

End Sub

Private Sub Workbook_Open()

  Application.OnTime Now + TimeValue("00:15:00"), "MyMacro"

End Sub
Now in any Standard Module (Insert>Module) place this;

Public dTime As DateSub MyMacro()dTime = Now + TimeValue("00:15:00")Application.OnTime dTime, "MyMacro"

'YOUR CODEEnd Sub
Note how we pass the time of 15 minutes to the Public Variable dTime This is so we can have the OnTime Method cancelled in the Workbook_BeforeClose Event by setting the optional Schedule  argument set to False. If we didn't pass the time to a variable Excel would not know which OnTime Method to cancel as Now + TimeValue("00:15:00")is NOT static, but is when passed to a variable. If we didn't set the optional Schedule  argument set to False the Workbook would automatically open every 15 minutes after closing it and run MyMacro

-----

multiple good responses

FYI, there are some follow-up posts to this thread in another thread here:

Help with installing VBA code in Excel please (already have the code)
https://www.experts-exchange.com/questions/26629410/Help-with-installing-VBA-code-in-Excel-please-already-have-the-code.html