Pop up text box warning for cell > another cell

[Spurious Claim]

I have been using the following code which I got off another thread. The code works well if I manually enter the contents in column 'N', however my spreadsheet populates column 'N' from a SUMIF formula which seems to bypass the code.

DATA VALIDATION doesn't seem to work either (=SUM($H37-$N37)>0).

Any help on this would be much appreciated

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
Dim rngN As Range, rngH As Range
Set rngN = Application.Intersect(Target.EntireRow, Range("N1").EntireColumn)
Set rngH = Application.Intersect(Target.EntireRow, Range("H1").EntireColumn)
If rngN.Value > rngH.Value Then
MsgBox ("EXPENDITURE EXCEEDS LETTING")
Target.Value = ""
End If
End If
End Sub

 

[My Aswer Is This]

Please explain to me what you want the script to do.

 

[Spurious Claim]

Hi there, I want to flag and block the value in column N from being entered as a value greater than the value entered in column H. Both examples above seem to work when I manually enter a value under column N, but not when it is derived from a formula. thanks.

 

[My Aswer Is This]

Yes that's true.
Sheet change events only stop manual entries not entries as a result of a formula.
I know of no way of stopping that.