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]

I ran a quick little test. On my computer and over 24,000 cells: it took between 2 and 3 seconds to change the cell colors for only cells that had changed. Now, the time it took to read all the times on the monitor sheet and then change the colors for the watch page took up to 15 seconds.

On 1200 cells: it took 1 to 2 seconds for the cell change and over 3 seconds to read the times and change the color back.

How often are you polling for internet data?

Jeff

 

[vergaratarantino]

Hi Jeffrey and many thanks for your return.

Following the same structural discussion, here are my follow-ups to the items discussed before:

1) Worksheet_Change event do NOT trigger unless a direct change is made on the sheet by manually typing. I tried myself and observed that a cell linked outside to another sheet do not trigger the event if a change to the cell introduced through the source cell outside of the actual sheet. I am not sure if it is the case with DDE link data but I think this should be taken into consideration. I don't use Application.EnableEvents=False in my function as I said I cannot risk dropping any upcoming DDE feed data, even though I don't know whether that would cause it in the first place.

2) What worried me more was whether Application.OnTime would create a lot of scheduled tasks in memory so that Excel would crawl having to keep track of each cell that require a color change. As I understand, that bit of code creates a separate thread in memory and it might be very resource consuming if 500 cells require attention at peak. I feel lost though as to what Application.OnTime does.

3) I will try to check this issue out myself and report back soon. The situation with a DDE link feed might be different than a simple cell paste-link or a manual data entry. It will be a good chance to see if Worksheet_Change is fired when a DDE link is updated. I should have check that out much before already. My mistake.

4) My DDE code is not a Sub but a Function; therefore, from within that Function I cannot call a sub which accesses or changes cell properties directly. I think this is a game changer for your original thought. Therefore, I cannot get the time data written in the cells on the Monitor page. And, yes, what I was thinking originally was keeping track of time data in a series of arrays due to the restrictions of Excel I have just mentioned.

... and following up the next 2 questions in the subsequent post, originally not enumerated:

5) My infinite loop issue is another subject and I won't let it dilute the current problem I have been experiencing here and especially your kind assistance in resolving it.

6) In my code somewhere I use Shell to direct the execution to a Window program. When it executes and returns back to VBA code I noticed I lose a variable. The variable that I hoped to conserve its value after running Shell command was declared as Public but it always lost its value. Perhaps here comes the difference between Public and Global. Perhaps I should have used Global to keep the value of the Public variable. The reason might be totally different but I will try it asap. I could only test DDE related stuff during working hours, not in the evening. I will report on that too.

... following-up of your timing results in your last post:

7) That might require a revisit as I tried to explain in item 4 that mine is a function not a sub and I cannot write the timing data directly into cells. I am inclined to believe that once we -actually you, manage to integrate color change algorithm with a function rather than a sub, polling frequency of DDE data will be the last thing to worry about.

I hope I am clear enough Jeffrey, please let me know if I am not.

 

[vergaratarantino]

Hi Jeffrey,

I am now in a position to give you some feedback regarding several concerns enumerated before which I promised to put into a test:

1) A DDE link feed fires a Worksheet_Calculate event rather than Worksheet_Change. Therefore, I think this will be the major change in your original code. Worksheet_Change is only fired when a manual type of data entry is made on the sheet, in our case, "Watch" sheet. Setting EnableEvents=False does not hinder DDE link poll; however, it does prevent both Calculate and Change (actually all) events to execute. Therefore, in order not to cause any drop of incoming DDE data, I had better not disable system events at all.

2) I do now understand how Application.OnTime operates. I see that there is only one sub which calls itself on a scheduled time to see if there are any "MonitorCount" left to take care of. There is no any multitasking, parallel threads, handles, events etc as I at first though of for each and every cell to track its color. Very brilliant solution, I am truly very much impressed of your genious. So this item does not apply anymore, null and void. Cristal clear now.

3) Sorted out under item no 1.

4) I am still and very much behind my point here. In fact, it makes sense that Excel does not allow direct access of an active sheet through a function, because in any time it needs to keep track of all dirty cells which require a recalculation subsequent to any user input or system event. This too is pretty clear to me now. I cannot tell you enough how much I have learnt about Excel and had an opportunuty what I know and don't, just because of this thread.

5) Suspended... and if opportunity arises I would love to disscuss that particular issue of mine with yourself later; but probably not under this thread. I have to transform it to a tangible and easy-to-grasp problem in the first place, with a code sample preferably. I need to put some work on that first so there is nothing to discuss now.

6) I haven't been able to try that out- in fact that would require only replacement of the word Public with Global and see if the variable loses its value on the fly. However, I strongly think that would make no difference. I believe the problem is caused by something else. Leave this with me. I will handle it myself.

7) I am still behind that statement. Mine is, and have to be, a function not a sub. This item is parallel to item 4. No need to repeat here again.

All and all Jeffrey, I have now a grasp of the difficulties you are probably facing. It turns out that, in order to correctly capture a DDE update, Worksheet_Calculate should be used. However, unlike Worksheet_Change event, Worksheet_Calculate event does not accept any argument to keep track of cell changes through the keyword "Target". I really don't know how to get around of that problem and what other events there are available to comply with the logic and flow of your suggested and beautiful code, unfortunately.

Nevertheless, I hope you will find this complementary info helpful. I would be glad to hear from you if you come up with a solution based on current situation and requirements/restrictions.

I hope I am not pushing you so much.

 

[Jeffrey Smith]

All and all Jeffrey, I have now a grasp of the difficulties you are probably facing. It turns out that, in order to correctly capture a DDE update, Worksheet_Calculate should be used. However, unlike Worksheet_Change event, Worksheet_Calculate event does not accept any argument to keep track of cell changes through the keyword "Target". I really don't know how to get around of that problem and what other events there are available to comply with the logic and flow of your suggested and beautiful code, unfortunately.

Exactly, without knowing which cells have changed, how do you color them and how do you track if they need to be changed back. An elaborate method would have to be created to store the values of each cell (similar to what we did with the times) so when the DDE pulls data and fires the CalculateSheet, a macro would get called to check every cell for value changes and change the color. whew! But at the same time, if there was not a change during that iteration, then it checks to see when the last time it was changed so it can change the color back. As of a couple of days ago, I would have looked at a request like that and laughed. Now I know it's possible.

Ok, you saw my timing tests... based on 1200 cells, which is quite small, do you think the macro could do all that between DDE pulls and still allow the user to edit cells and navigate?

Jeff

 

[vergaratarantino]

Exactly, without knowing which cells have changed, how do you color them and how do you track if they need to be changed back. An elaborate method would have to be created to store the values of each cell (similar to what we did with the times) so when the DDE pulls data and fires the CalculateSheet, a macro would get called to check every cell for value changes and change the color.

I have a solution for that problem. I already use functions with arguments to make sure that each cell status is taken care of. That is how it works: If a cell is changed by DDE feed, it fires a function as it is the argument of the function located in the adjacent cell, so one way or another I manage to get rid of having to have a Worksheet_Calculate and Change(Target) events. I keep track of the timing of the function call within the function and on each call I check if 5 minutes have past . If so, function returns a specific value and a ConditionalFormat verifies continuously to change the color of the subject cell if function returns a specific value. The only deficiency with this algorithm is that if DDE data is not updated, say, for 10 minutes, then the cell color stays as it is until next time function is called. The cell has to wait till next time DDE link updates the cell to trigger eventually a function call. This method therefore quite depends on how frequently DDE data is pulled. And it sometimes takes more than an hour so I cannot get a fixed 5-minute color update in my Excel sheet. When I decided to open this thread I was just in search of a more time-aware algorithm that could provide an exact 5-minute update. Therefore, I loved your OnTime solution.

Some food for thought: Am I wrong to think that a true solution could be obtained by integrating both my function-call algorithm and your OnTime method. I just can't get my head around though. Perhaps, you might have more ideas as to how it could be achieved.

 

[Jeffrey Smith]

Ok, whole new animal, well almost new.

Try these new procedures. Replace all original code of mine with this. Please test.

'Standard Module

Global ChangedAry(10 To 100, 10 To 500) As Integer
Global DDEValues(10 To 100, 10 To 500) As Double
Global Changes As Integer
Global MonitorCount As Long
'
'
This is an example of your function that gets the DDE.  You will have to add the code I have into yours
Function GetDDE(R As Range) As Variant
  Dim V As Double
  Dim T As Long
  Dim Addr As String
  Dim Rw As Long, Col As Long
  
  'Call the DDE and declare value of function
  GetDDE = 9   'This is a test statement
  Addr = R.Address
  
  
  'Get Tracking value
  V = Sheets("Track").Range(Addr).Value
  
  If V <> GetDDE Then  'The last value does not equal the new value
    DDEValues(R.Column, R.Row) = GetDDE
    ChangedAry(R.Column, R.Row) = 1
    Changes = 1
  Else                  'It does equal; check the time
    If Sheets("Monitor").Range(Addr) >= Now() Then  'Beyond the time; reset color
      ChangedAry(R.Column, R.Row) = -1
      Changes = 1
    End If
  End If
  
  
End Function

'Only called after Worksheet calculate
Sub CheckChanges()
  Dim X As Long
  Dim Y As Long
  For X = 10 To 100
    For Y = 10 To 500
      If ChangedAry(X, Y) = 1 Then
        Sheets("Track").Cells(Y, X) = DDEValues(X, Y)
        Sheets("Monitor").Cells(Y, X) = Now() + TimeValue("00:00:10")
        ChangedAry(X, Y) = 0
        With Sheets("Watch").Cells(Y, X).Interior
          .Pattern = xlSolid
          .PatternColorIndex = xlAutomatic
          .ThemeColor = xlThemeColorDark2
          .TintAndShade = -9.99786370433668E-02
          .PatternTintAndShade = 0
         End With
      ElseIf ChangedAry(X, Y) = -1 Then
        With Sheets("Watch").Cells(Y, X).Interior
          .Pattern = xlNone
          .TintAndShade = 0
          .PatternTintAndShade = 0
        End With
        Sheets("Monitor").Cells(Y, X).ClearContents
        ChangedAry(X, Y) = 0
      End If
    Next Y
  Next X
  Changes = 0
  Call CheckWatch
  
End Sub
 
'
'This Sub changed
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 MRng.Value <= Now() Then
      With Cell.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
      End With
      MRng.ClearContents
      MonitorCount = MonitorCount - 1
    End If
  Next Cell
  
  Application.EnableEvents = True
  
  If MonitorCount > 0 Then
    Application.OnTime Now() + TimeValue("00:00:05"), "GoWatch"
  End If
    
End Sub
'
'
'This Sub Changed, maybe
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

'
'Add this code to the "Watch" sheet module

Private Sub Worksheet_Calculate()
  If Changes > 0 Then Call CheckChanges
  
End Sub

 

[Jeffrey Smith]

What I didn't tell you is that you need to create a new sheet called "Track". Also, the GetDDE function I created needs to have the address of the range the function resides. Meaning, the function should look like this:

=GetDDE(A2)

Where A2 is the cell the function is in.

Jeff

 

[vergaratarantino]

Hi Jeffrey,

I cannot tell you enough how I am sorry for not returning so long. I was and still am unable to try your updated code. Please bear with me for a few days more.

After all your being so kind for my individual issues and the time you have spent on them, I am sure you deserve a better behaviour from me as the initiatior of this thread and as the one who actually demanded some help in the first place. Truly sorry for that. I will return at my earliest opportunity, I assure you. I simply want to give enough time that your beautiful code deserves. I trust you would understand. Many many many thanks. Talk to you soon.

 

[Jeffrey Smith]

Hey no problem. All good. We both have this thing called "work" we have to do... I know, no swearing with those 4 letter words (stoopid need to make a living!). I'm still not too old to be adopted by a very wealthy family.

I think we're close on solving this, I hope.