VBA: Worksheet Change Event Not Comparing Number & Formula Value

[mykulpasskwa]

I have the below code that isn't doing what I'd like and I think I know what the issue is but I can't figure out the solution. Basically there are two cells "ShBal" and "BanBal" with numbers and if they match then the Match subroutine runs. BanBal is manually entered by the user and is constant. ShBal is a Sum formula that adds together all the cells above it.

If I enter all the data I need and then enter a number into the BanBal cell then the Match subroutine runs just fine and it asks me to save like I want it to. If I enter the BanBal when the sheet opens--a userform asks you for BanBal when the workbook first opens (among other information)--then VBA skips over the Call Match line which means I don't know when the two numbers match and I am not prompted to save.

I think the problem is that ShBal is a formula and not a number, so VBA for whatever reason isn't picking up that the number is changed or that the two numbers match. I just can't see what I can do to fix that. I'm thinking it needs to compare ShBal.Value and maybe BanBal.Value? That's just a shot in the dark.

I'm open to other suggestions if there isn't a way, but the reason I have it set up like this is so the user knows when to stop working and offers them a chance to save before they lose the data for any reason.

Private Sub Worksheet_Change(ByVal Target As Range)

'PURPOSE: Anytime the sheet changes, code checks if sheet balance and Banner balance match.
    If Not (Application.Intersect(ActiveSheet.Range("ShBal", "BanBal"), Target) Is Nothing) Then
        Call Match
            Else
    End If

'Another sub

End Sub

 

[mykulpasskwa]

I figured it out. All I had to do was move it to the Calculate event and dim the variables. ?‍ I'll leave this post up and here is what the code looks like now:

Private Sub Worksheet_Calculate()
'PURPOSE: Anytime the sheet changes, code checks if sheet balance and Banner balance match.
Dim Target As Range
Set Target = Range("ShBal")
    
    If Not Application.Intersect(ActiveSheet.Range("ShBal"), Target) Is Nothing Then
        Call Match
            Else
    End If
End Sub