VBA fuction to alert when value changed with in a time frame

Question

I have a excel cell where its value is updated every minute.

What i need is a alert message box; while that cell value moves to a particular time.

Suppose a cell has value 10

if it reaches 7 in one minute .. i need a message box to alert that.

if it is not reaching 7 in one minute its not needed to be alerted.

Please help to write a macro for this

Answer 1

Inside VBA editor for ThisWorkBook, you could write the following code

Dim WithEvents SheetToWatch As Worksheet

Private Sub SheetToWatch_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
    If Target.Value = 7 Then
        MsgBox "changed to 7"
    End If
End If
End Sub
Private Sub Workbook_Open()
    Set SheetToWatch = Sheets("Sheet1")
End Sub

Basically the code sets the reference of Sheet1 to the variable SheetToWatch when the workbook opens. Declaring the variable using WithEvents lets one capture the events.

I am using Change event of the worksheet and checking if the cell that was changed is A2. If so, I am checking or comparing the value with 7 (it can be dynamic and depends on how you want to handle it).

See if this helps.

Answer 2

Cannot be done with a function, any way that I can think of.

If you control the mechanism that is updating the cell, then you could it call a VBA subroutine that you wrote, instead, have to send the alert and then update the cell from that routine.

If you do not control the updating mechanism, then the only thing that I can think of that might work is of the Cell, Range or Worksheet classes have a ChangedDate event that you could catch from VBA and do your alerting from there.