Solved

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

Posted on 2013-05-18
37
260 Views
Last Modified: 2013-12-20
I would like to be able to dynamically change the color of a cell in an Excel spreadsheet based on the time of day. For example, 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. This formula would be applied to specific cells, not the entire spreadsheet.
0
Comment
Question by:babyb00mer
  • 14
  • 12
  • 9
  • +2
37 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39177483
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.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39177484
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
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 333 total points
ID: 39177499
Please find attached a sample workbook containing two different approaches; one using Visual Basic for Applications code, & another using Conditional Formatting (like ssaqibh provided above... albeit with a different Formula for each condition).

These approaches address your original question (but not my subsequent confirmation query).
Q-28132294.xls
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 39177548
0
 

Author Comment

by:babyb00mer
ID: 39177853
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.
0
 
LVL 35

Assisted Solution

by:[ fanpages ]
[ fanpages ] earned 333 total points
ID: 39179590
Hi again,

This code should be within the (This)Workbook code module:

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

  On Error Resume Next
  
' Stop the current Timer event (if applicable)...

  Call Stop_Timer
  
End Sub
Private Sub Workbook_Open()

  On Error Resume Next
  
  Call Check_Time
  
End Sub

Open in new window


This code should be in a new (Public) Code Module, named "basQ_28132294":

Option Explicit

Public datCheck                                         As Date

Private Const strProcedure                              As String = "basQ_28132294.Check_Time"
Public Sub Check_Time()

' ------------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28132294.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               28132294
' Question Title:   Is it possible to change the color of the cell in an Excel spreadsheet based on the time of day?
' Question Asker:   babyb00mer
' Question Dated:   2013-05-18 at 18:42:09
'
' Expert Comment:   fanpages
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited / N.Lee [ http://NigelLee.info ]
' ------------------------------------------------------------------------------------------------------------------

  Dim intHour                                           As Integer
  Dim lngColorIndex                                     As Long
  
  On Error Resume Next
  
' Stop the current Timer event (if applicable)...

  Call Stop_Timer
  
' Store the current Hour...

  intHour = Hour(Now())
  
' 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 (intHour >= 0 And intHour < 8)
          lngColorIndex = 6&
      
      Case (intHour >= 8 And intHour <= 23)
          lngColorIndex = 3&
      
      Case Else
          lngColorIndex = xlNone
      
  End Select
  
' Applied to specific cells, not the entire spreadsheet...

  Worksheets("Q_28132294").Range("TIME_SPECIFIC").Interior.ColorIndex = lngColorIndex
  
' Start the (new) Timer event...

  Call Start_Timer
  
End Sub
Public Sub Stop_Timer()

  On Error Resume Next
  
  If datCheck > CDate(0#) Then
     Application.OnTime EarliestTime:=datCheck, _
                        Procedure:=strProcedure, _
                        Schedule:=False
                     
'    Debug.Print Format$(Now(), "dd/mm/yyyy hh:mm:ss") & " Timer Stopped"
     
     datCheck = CDate(0#)
  End If ' If datCheck > CDate(0#) Then

End Sub
Public Sub Start_Timer()

  On Error Resume Next
  
' Note: If lngTimeout is set to anything other than 0, the value represents the number of seconds when this routine will be re-executed.
'       Otherwise, the Timer is set at the start of the next hour.

  Const lngTimeout                                      As Long = 0&
  
  If datCheck = CDate(0#) Then
     If lngTimeout > 0& Then
        datCheck = Now() + TimeSerial(0, CInt(lngTimeout \ 60&), CInt(lngTimeout Mod 60&))
     Else
        datCheck = Now() + TimeSerial(0, 60 - Minute(Now()), 0)
     End If ' If lngTimeout > 0& Then
        
     Application.OnTime EarliestTime:=datCheck, _
                        Procedure:=strProcedure
  
'    Debug.Print Format$(Now(), "dd/mm/yyyy hh:mm:ss") & " Timer Started for " & Format$(datCheck, "dd/mm/yyyy hh:mm:ss")
  End If ' If datCheck = CDate(0#) Then

End Sub

Open in new window


I have added a new sample workbook for convenience.

BFN,

fp.
Q-28132294b.xls
0
 
LVL 80

Expert Comment

by:byundt
ID: 39179595
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

Open in new window

Brad
Q-28132294-1.xls
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39179612
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! :)
0
 
LVL 80

Expert Comment

by:byundt
ID: 39179616
I agree. You beat me on that one.

Brad
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39179627
...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)?
0
 
LVL 80

Expert Comment

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

Is there a benefit to it?
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39179755
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.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39179760
Thanks!
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39179761
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.
0
 
LVL 80

Expert Comment

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

Author Comment

by:babyb00mer
ID: 39180038
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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39180095
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.
0
 

Author Comment

by:babyb00mer
ID: 39182065
Again, I apologize.

Please refer to the document I've attached. I hope it's adequate.
Example.docx
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39182105
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.
0
 

Author Comment

by:babyb00mer
ID: 39182756
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
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39190188
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.
0
 

Author Comment

by:babyb00mer
ID: 39192886
At most, the start and end times might occur at half hour increments, but I'm willing to compromise. :)
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39193006
...but are these intervals always "on the hour" (or half hour), at any number of minutes past a given hour?
0
 

Author Comment

by:babyb00mer
ID: 39194811
Yes. For example, 1:00, 1:30, 2:00, 2:30, etcetera.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39194829
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?
0
 

Author Comment

by:babyb00mer
ID: 39196130
No. All times will be on the hour or half hour.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39196341
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.
0
 

Author Comment

by:babyb00mer
ID: 39203538
I agree.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39203590
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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39203794
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.
0
 

Author Comment

by:babyb00mer
ID: 39242890
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.
0
 

Author Comment

by:babyb00mer
ID: 39631961
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
0
 
LVL 80

Expert Comment

by:byundt
ID: 39631980
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
0
 

Author Comment

by:babyb00mer
ID: 39632060
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.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39632122
In that case, I'll defer to fanpages.
0
 

Author Comment

by:babyb00mer
ID: 39690231
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.
0
 
LVL 80

Accepted Solution

by:
byundt earned 167 total points
ID: 39690576
I'm sorry to hear about your loss and the problems with your mother health.

We will keep the question open for you.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now