Show text in a cell when another cell changes value.


I am streaming some data and using the code below to capture the highest

point in the data and display it in a cell. When a new high value is reached

the cell will update. All I need is  text to appear in another cell

when the value is updated.

I need the text to show for a time period of a few seconds only.

If it is possible, I would like to be able vary the time period the text appears,

and change the text as necessary.

The text would be "read" by another sheet which will carry ot a function.

Private Sub Worksheet_Calculate()

If [A1] > [A2] Then

    [A2] = [A1]

End If

End Sub



Selected Answer

Install the procedure below in the code sheet of the worksheet on which you want the action. This location is important. The code won't work if installed elsewhere.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Variatus @TeachExcel 20 Feb 2020

    Const Indicator As String = "B2"        ' change to suit
    Dim EndTime As Double

    If Target.Address = Range("A2").Address Then
        If Target.Value > Range("A1").Value Then
            Application.EnableEvents = False
            Range(Indicator).Value = "NEW HIGH"

            EndTime = (Now + TimeValue("0:00:05"))      ' 5 seconds
                DoEvents            ' keeps excel doing other other tasks
            Loop Until Now >= EndTime

            Application.EnableEvents = True
        End If
    End If
End Sub

Adjust the code. (1) Replce cell addresses A1, A2 and B2 with those that suit you better. (2) Replace "NEW HIGH" with whatever you want to be shown and format the cell red or green as you wish (font and background formatting isn't done by the code). (3) Change the 5 seconds the above code will show the message to whatever you prefer, taking care not to change the format.



Thanks for this. I seems to be partially working.
I see "NEW HIGH" in a cell as soon as I copy
and paste the link from the sheet where I am streaming
the data form. After that it doesn't seem to re-set and record
a newer high.
Potluck (rep: 2) Feb 20, '20 at 12:32 pm
No. I thought that's what your stream does. My code reacts to changes in A2. If you need the high that triggered the alert to be recorded in A1 you can insert that in the code, just after displaying the alert and before setting EndTime. Note that no new high can be reacted to while the alert is shown.
Variatus (rep: 3543) Feb 20, '20 at 7:34 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login