change color of a cell for some time based on its value that is not manually entered

[vergaratarantino]

Hi guys,

Could someone suggest me a VBA macro to change the color of a cell automatically for a specific period of time -say 5 minutes, based on the value the subject cell holds at that time. The cell value is not manually entered but comes from a sub.

There will be hundreds of such cells so that the macro must be able to be repeated for other cells utilizing their individual cell values as well.

 

[Jeffrey Smith]

There are ways to monitor the sheet for changes. When a change occurs it could check a stored time. If the time is more than 5 minutes past, then it changes the color of a range of cells back to white. Problems I see:

* You wouldn't be able to track individual cells.
* Your SUB would have to make the color change according to your specs when it ran.

 

[vergaratarantino]

I am sorry but there must be a seperate 5 minute waiting time in color for each individual cell, no stepping back from this condition. Also, I cannot use a sub because I use several functions which would not allow me access and make changes to cells direct due to some restrictions of Excel calculation and having to keep track of everything that change on the whole workbook. 2 out of 2. It looks like a total failure. Deosn't it? I hope I am wrong.

 

[Jeffrey Smith]

It would take some creativity. Me just thinking out loud. Also encouraging others to pipe in.

If (a big if) you could record the times for each cell on a different sheet called, let's say, "Monitor". For each cell that you want to monitor on the "Watch" sheet, a time would be saved at the same address on the Monitor sheet. When a change occurs or even a cell selection occurs on the Watch sheet, the macro would read each individual time on the Monitor Sheet to see if 5 minutes has passed, thus changing the color of the cells on the Watch sheet.

Ok, so the question is: how many cells do you want to watch? If there are a lot, it may a very slow update.

Jeff

 

[Jeffrey Smith]

Ok, Here's my first swing. It definately needs to be catered to your specific needs.


I created a sheet called "Watch". this sheet contains the cells you want to have changed color every 5 minutes. I created a sheet called "Monitor" that has nothing on it except the times the cells changed. I created a named range on the Watch sheet called "Watch_Rng". These are the cells you want to monitor.

I put this code under the sheet name Watch. The colors may need to be changed to your needs. GoWatch gets called every 5 seconds after the sheet is activated. You can change that to whatever you need.

Private Sub Worksheet_Activate()
  Call CheckWatch
  
End Sub

Private Sub Worksheet_Calculate()
  Call CheckWatch
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
  Dim i As Range
  Dim Cell As Range
  Dim MSht As Worksheet
  
  Set MSht = Sheets("Monitor")
  
  Set i = Intersect(Range("Watch_Rng"), Target)
  If Not i Is Nothing Then
    Application.EnableEvents = False
    With i.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = -9.99786370433668E-02
        .PatternTintAndShade = 0
    End With
    For Each Cell In i
      MSht.Range(Cell.Address) = Now()
      MonitorCount = MonitorCount + 1
    Next Cell
    Application.EnableEvents = True
  End If
  Call GoWatch
      
End Sub

I put this code in a standard module. MonitorCount stores the number of cells that have changed and need to be changed back. IF MonitorCount is zero, then the GoWatch Sub does not need to be called on a regular basis.

Global MonitorCount As Long
Public Sub GoWatch()
  Dim Cell As Range
  Dim MSht As Worksheet
  Dim MRng As Range
  Dim Addr As String
  
  If MonitorCount = 0 Then Exit Sub
  
  Application.EnableEvents = False
  
  Set MSht = Sheets("Monitor")
    
  For Each Cell In Sheets("Watch").Range("Watch_Rng")
    Set MRng = MSht.Range(Cell.Address)
    If Len(MRng.Text) > 0 And Now() - MRng.Value >= TimeValue("00:00:10") Then 'Can be changed to 5 minutes
      With Cell.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      MRng.ClearContents
      MonitorCount = MonitorCount - 1
    End If
  Next Cell
  
  Application.EnableEvents = True
  
  If ActiveSheet.Name = "Watch" And MonitorCount > 0 Then
    Application.OnTime Now() + TimeValue("00:00:05"), "GoWatch"
  End If
    
End Sub
'
'
Sub CheckWatch()
  Dim MSht As Worksheet
  Dim Addr As String
  
  Set MSht = Sheets("Monitor")
  Addr = Sheets("Watch").Range("Watch_Rng").Address
  MonitorCount = Application.WorksheetFunction.CountA(MSht.Range(Addr))
  If MonitorCount = 0 Then Exit Sub
  Call GoWatch
End Sub

 

[vergaratarantino]

Ok, so the question is: how many cells do you want to watch? If there are a lot, it may a very slow update.

In direct response to your first question: There would be 1200 cells in total, 30 rows by 40 columns. Yes I can keep track of timing for each cell, that wouldn't be a problem. There is no a big "if".

I am impatient to read your next response and try the code you put in there...

 

[vergaratarantino]

Wow!

You are a genius Jeffrey and your code you put in here is simplya piece of art.<o></o>

I just need to tailor it for my particular need:

1) The cells in the “Watch” sheet get data automatically through DDE link from an external source; therefore, I don’t manually enter anydata which would have otherwise triggered Worksheet_Calculate orWorksheet_Change events. Therefore cell colors would not change with your code. We need another trigger here to actuate repainting “Watch” sheet. Particularly, is there an event that activates when a DDE link data is updated?

2) Considering 1200 cells to be painted when cellvalues change, do you think your code requires a lot of cpu power and would make Excel crawl if it checks “Watch” sheet every second and, say, there are 500 cell-updates in that second?<o></o>

3) Am I right to think that EnableEvents=False might cause some precious DDE data might drop if an update comes in that particular time frame during which the setting was set to False? I need to make sure that other functions will be able to accept new DDE updates and get properly calculated while system events are disabled.<o></o>

4) I was hoping to keep track of time for cell updates in memory rather than a separate “Monitor” sheet. There are reasons for that. I have to use functions as opposed to Subs as everything is calculated automatically in my code (I don’t call functions, press buttons, enter data manually) therefore I cannot have direct access to cells to change their color, font, content etc. This is a weird restriction of Excel about which I cannot say I have understood completely, but I have been convinced that I have to obey that rule unless I want to end up with #VALUE as my function return in the calculated cells. Is there a way to keep time tracking in memory with variables as opposed to another sheet? Or you may say that the solution you suggest is not a problem in my case at all. As I said I don’t understand the rationale behind it, I cannot comment.

Many thanks Jeffrey, your code works perfectly. Truly, it isjust my capricious code that requires mother care here.<o></o>
<o></o>
<o></o>
<o></o>

 

[vergaratarantino]

Being impatient for your reply, the more I read your code, the more I love it. I have almost memorized each and every line. In addition to my previous post:

Why do you use EnableEvents=False both on GoWatch Sub and Worksheet.Change Event? I have an infinite loop issue in another code of mine where I use Application.OnTime, and I hope this could give me a hand to identify the problem. Would you say, without setting EnableEvent to False, your code too would cause infinite loops when there are a lot of cell updates through DDE external links?

I also notice "Global" declaration on MonitorCount. Global type of declaration is nowhere mentioned. What is the difference between Public and Global? Would "Public MonitorCount as Long" do the same job?

 

[Jeffrey Smith]

Ok, what step at a time. I knew you would have code to add values to the cells. This is a beginning. To asnwer your questions:

1) You should call GoWatch when the DDE updates. Usually this sub (Worksheet_Change(ByVal Target As Range)) gets called whenever a change is made to the sheet. If you have Application.enableEvents = False in the DDE code then that would stop it from happening. We could alter your code to use some of the the code in Worksheet_Change.

2)500 cells is not a lot for Excel to do. It depends on the speed of your computer. Do you need to update every second? Maybe 3 or 5 seconds. We could use the Application.Screenupdating = False to suppress the color change for a second.

3)I think a test is in order. My first response is that I think as soon as any code is running and a OnTime has fired, that Excel will follow that trigger immediately afterward.

4)You can have Functions read cell values. Your DDE code is a SUB, right? The DDE code can all any other SUB which, in this case, writes the Times in the cells on the monitor page. Your functions would not be invloved with the color changes. I don't the details ironed out, but it does seem like you could track the cell times using an Array(). I think the time difference for storing the times in 500 cells VS an Array() is insignificant. The problem with Arrays() are that as soon as you reset your VBA code, all times would be lost.

You will need to decide in your DDE code what to color the cells when the data is imported.

Let me know if I didn't anser all your questions.

Jeff

 

[Jeffrey Smith]

To answer the Application.EnableEvents question: Whenever anything changes on a sheet, Excel checks to see if it needs to do anything, such as calculate or run some code. Turning events off makes the code go faster. GoWatch is called seperately from Worksheet_Change so it has to be done on both.

I think we should incorporate the GoWatch into your DDE code. This would eliminate one of the two Application.OnTime Calls. I'm going to see if two seperate OnTime's can run simultaneously.

Global is used to allow a variable acces to forms, other modules, and I think other workbooks. The overhead difference for Global vs Public is nothing. I tend to use Global more because I got tired of having to make the switch when my code reached out and expanded over time. I would always be debugging trying to find out why the variable wasn't changing. That can be annoying. In this case, Public would work just fine.

Jeff