Detect cell change (formula)

[TheWennerWoman]

Hello,

I have a formula in cell J2 that is

=EXACT (A2, B2)

I want to detect if this changes from TRUE to FALSE or vice versa.

I have tried

Private Sub Worksheet_Calculate()
Static MyOldVal

If Range("J2").Value <> MyOldVal Then
MsgBox "Hello"
MyOldVal = Range("J2").Value
End If
End Sub

However this always fires (wrongly) the first time something is entered on the sheet and J2 recalculates. After that, it works perfectly. How can I stop it triggering the first time?

Thanks for reading.

 

[breynolds0431]

Hi. What about a worksheet change event focused on cells A2 and B2?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim kcell As Range: Set kcell = Range("A2:B2")
Static MyOldVal

If Not Application.Intersect(kcell, Range(Target.Address)) Is Nothing Then
    If Range("J2").Value <> MyOldVal Then
        MsgBox "Hello"
        MyOldVal = Range("J2").Value
    End If
End If
End Sub

 

[TheWennerWoman]

Hi. What about a worksheet change event focused on cells A2 and B2?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim kcell As Range: Set kcell = Range("A2:B2")
Static MyOldVal

If Not Application.Intersect(kcell, Range(Target.Address)) Is Nothing Then
    If Range("J2").Value <> MyOldVal Then
        MsgBox "Hello"
        MyOldVal = Range("J2").Value
    End If
End If
End Sub

Apologies, A2 and B2 are also formulae.