Is it possible to change the color of the cell in an Excel spreadsheet based on the time of day?

Hi,

Firstly, yes, that is possible.

However, just to confirm:

Would you wish the cell(s) to change colo(u)r when the alternate time period is reached?

That is, if the workbook was opened at 11:55pm, would you wish the cell(s) to change from red to yellow once 12midnight was reached (without having to re-open the workbook, or refresh the worksheet being viewed)?

BFN,

fp.

Select the cells and color them red
While the cells are selected:
from the format menu select conditional formatting
From the first box select Formula is
In the next box paste this formula
=NOW()-TODAY()<1/3
click on the format button
select the Patterns tab
select the color yellow
click OK
click OK

Nigel,

Post something here:

https://www.experts-exchange.com/questions/28051029/Excel-Zone-Expert-Discussion-Number-34.html

Kevin

In the original post, fanpages wrote...

That is, if the workbook was opened at 11:55pm, would you wish the cell(s) to change from red to yellow once 12midnight was reached (without having to re-open the workbook, or refresh the worksheet being viewed)?

My answer is yes.

If you want the color of the cells to change without the user lifting a finger, then you need to use Application.OnTime method. This will schedule an alarm to occur at 8:00 AM and midnight to change the color of the cells--as long as Excel is running. The workbook will be opened if necessary.

Conditional formatting (as suggested by both ssaqibh and fanpages) will change at 8:00 AM and midnight--but the user must be doing something with the workbook (such as data entry) to trigger the color change.

A Workbook_Open sub as suggested by fanpages will open the workbook with the correct colors, but won't switch them if the workbook remains open past the 8:00 AM and midnight trigger points.

To accomplish the Application.OnTime scheduling of the color changes, put all of the following code in the ThisWorkbook code pane:

Private Sub Workbook_Open()
Alarming
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim Earliest As Double
Earliest = Date + IIf((Now() - Date) < TimeSerial(8, 0, 0), TimeSerial(8, 0, 0), 1)
Application.OnTime Earliest, "ThisWorkbook.Alarming", , False
End Sub

Private Sub Alarming()
  Dim lngColorIndex                                     As Long
' If the spreadsheet is opened between midnight and 8:00 AM, the cell(s) would be yellow.
' Conversely, if the spreadsheet is opened between 8:00 AM and midnight, the cell(s) would be red.

  Select Case (True)
      Case (Now() - Date) < TimeValue("8:00")
          lngColorIndex = 6&
          Application.OnTime Date + TimeSerial(8, 0, 0), "ThisWorkbook.Alarming"
       Case Else
          lngColorIndex = 3&
          Application.OnTime Date + TimeSerial(24, 0, 0), "ThisWorkbook.Alarming"
  End Select
  
' This formula would be applied to specific cells, not the entire spreadsheet.

  Worksheets("Q_28132294").Range("TIME_SPECIFIC").Interior.ColorIndex = lngColorIndex
End Sub

Select allOpen in new window

Brad
Q-28132294-1.xls

Brad:

A Workbook_Open sub as suggested by fanpages will open the workbook with the correct colors, but won't switch them if the workbook remains open past the 8:00 AM and midnight trigger points.

I suggest you look at my comment (code, &/or sample workbook) three minutes before your own! :)

I agree. You beat me on that one.

Brad

...What is a few minutes between friends? :)

Talking of minutes, did you spot that my code calculates the number of minutes until the next hour & sets the Application.OnTime event to trigger "at the top of the hour" (rather than in 8 hours, or in 24 hours, like your code does)?

I was more amazed at your cheeky renaming of ThisWorkbook. First time I'd noticed that being done.   :-)

Is there a benefit to it?

If you find any of my (example) workbooks that have not renamed ThisWorkbook to match the (VB) Project name then this is an exception to my "rule" & is not intentional.

I do it so that if I have more than one workbook open I can see from the Project window which workbook I am editing code within.

Thanks!

PS. Just to add that using the Conditional Formatting approach, as soon as a Calculation is performed on the worksheet (for example, by manually pressing function key [F9]) when a new time period has been reached, the colo(u)r of the appropriate range is changed.

Even data entry suffices to trigger the Conditional Formatting update. I tested it on a workbook with no formulas (except in the Conditional Formatting).

Wow! I'm overwhelmed by the response!

I haven't had a chance to read all of the responses yet, but it occurred to me that I left out a piece of information.

The condition on which the formatting depends will be a range defined by the value of two other cells. The value of one cell will define the beginning of the interval, and the value of another will define the end. When the time of day falls within that range, the color of  other cells will change. I hope that makes sense.

Yes, it makes sense, babyb00mer, but if this is (now) the extent of your question you will need to provide additional information to confirm your exact (revised) requirements.

Brad: Sorry, yes, I realised that; what I was endeavouring to relay was that the Application.OnTime event could perform a Calculate method on the defined Range so that even the Conditional Formatting approach could be used in this manner.

Again, I apologize.

Please refer to the document I've attached. I hope it's adequate.
Example.docx

The second image/description seems incorrect.

At 2:00 AM the fill color of A1 and A3 would change…

I suggest:
[A1] should change at 2am.
[A3] should change at 12midnight.

Also, the final image/description seems incorrect.

…because 2:00 AM falls within the ranges defined by B1:C1 and B3:C3, respectively. At 9:00 PM, only A2 would be highlighted.

I suggest:
At 9pm, [A2] would not be highlighted.
At 10pm, it would.


If either/both of these suggestions is not as you intended then, I am sorry, but your requirements are unclear.

You are right, I misspoke. However, it's only the cells in the first column for which the colors should change. I want the fill color of the cells in column 1 to change based on the criteria established by the values in columns 2 and 3. Columns 2 and 3 define the start and end time. In order for the color of column 1 to change, the current time must be greater than or equal to column 2 and less than or equal to column 3. Each cell in column 1 has its own set of parameters. For example, the values in C2 and C3 have no effect on A1 or B1. I have attached a new example.
Example-2.docx

Are the times you wish to monitor always at the beginning of an hour (i.e. zero minutes), or can the minutes past the hour be variable?

I am concerned that if you have many rows of times with differing conditions that the number of timers required to monitor the current time compared to the time range specified per row will drastically impede perform of Microsoft Excel (& your PC in general).

If all times start/finish exactly on a given hour, then only one timer (to monitor all rows) is required.

At most, the start and end times might occur at half hour increments, but I'm willing to compromise. :)

...but are these intervals always "on the hour" (or half hour), at any number of minutes past a given hour?

Yes. For example, 1:00, 1:30, 2:00, 2:30, etcetera.

I may not be being clear in my question(s), of you are missing my point.

Can the mix of times across all rows also be, for example, 1:23, 1:29, 1:46, 1:53, 2:02, 2:16, 2:30, 3:27, and also, 1:00, 1:30, 2:00, 2:30?

No. All times will be on the hour or half hour.

OK, thanks.

This means...

The first time the workbook is opened a "check" (* see below) will be made on all the rows.

A timer will then be set so that a subsequent "check" will occur at the next nearest half hour, or next nearest full hour, depending on the present time.

Once that "check" has been performed, a new timer will be set for 30 minutes thereafter.

* A "check" will involve finding all rows where the current system time (ignoring the system date) falls between the time value in column [ B ] & the time value in column [C].

On the rows where this is the case, column [A] will be set to have a yellow interior (background) colo(u)r.

On the rows where this is not the case, column [A] will be set to the default (automatic)/typically white interior (background) colo(u)r.

The code provided above is not required & should be treated as redundant/extraneous to your requirements even though it answered your (initial) question.

If you agree, then I think we have a definitive specification for your (revised) requirements.

I agree.

At some point fanpages will give you some code using Application.OnTime that triggers every 30 minutes to color your cells.

I personally would use Conditional Formatting as suggested by Saqibh way back at the beginning. No VBA code to maintain, and the colors will switch as soon as the user enters any data in the workbook (assuming that you just passed one of the switching times). For example, you might use a formula criteria like:
=AND(A1<>"",OR(B1="",MOD(NOW(),1)>=B1),OR(C1="",MOD(NOW(),1)<=C1), COUNT(A1:B1)>0)

MOD(NOW(),1) is a convenient way of returning the time.

I put the OR function in there so if either B1 or C1 are blank, that part of the criteria is deemed satisfied.

I put the COUNT function in there so you have to have at least one time entered in columns B and C. I put the test for A1 being blank so you don't color a cell with no message.

The issue with simply using Conditional Formatting, Brad, is as you have previously mentioned:

Conditional formatting (as suggested by both ssaqibh and fanpages) will change at 8:00 AM and midnight--but the user must be doing something with the workbook (such as data entry) to trigger the color change.

When using an Application Timer: if an edit is not taking place, or a dialog window is not open, the Visual Basic for Applications code will run & change the colo(u)r of the cell(s).

If an edit is taking place, as soon as this completes, the cell(s) will change colo(u)r.
If a dialog window is open, as soon as this is closed, the cell(s) will change colo(u)r.

Also, please don't forget that the specification of the request has changed somewhat since we first discussed Conditional Formatting.

babyb00mer: If you wish to proceed with Brad's suggestion, please advise accordingly.  Thank you.

Sorry folks. I haven't forgotten about you. I've just been busy. I hope to get back to this by the beginning of next week.

So here's the deal...

In an effort to clarify my objective, I'm attaching the spreadsheet.

What I'd like is for the color of the cell in column E to change when the current time is within the range specified by columns N and O. When the current time is within range, the color should be green. When the current time is outside of the range, the color should be red. One of the challenges is that the "window" can start in one day and end in another.
Attachment.xlsx

babyb00mer,
Before we take a stab at suggesting a solution, could you please decide whether the cell color must change automatically without the user doing anything (VBA will be required) or if the user will be actively working with the workbook and merely needs a visual reminder (VBA is not required).

Brad

The color change should be automatic. The intended audience will not be modifying the document. Its content will remain relatively static. But each time it is opened - or even while it's open - the cell colors should be updated automatically.

In that case, I'll defer to fanpages.

I'm trying not to neglect this question, but this year my father passed away and my mother went into the hospital. After all the work the experts have done, I'd rather not delete it. Other priorities have prevented me from devoting as much time as I'd like. If there was a way to suspend the question, I would do that. Please bear with me.