Pop up text box warning for cell > another cell

Spurious Claim

New Member
Joined
Mar 6, 2018
Messages
2
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,069
Office Version
  1. 2013
Platform
  1. Windows
Please explain to me what you want the script to do.
 

Spurious Claim

New Member
Joined
Mar 6, 2018
Messages
2
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

Well-known Member
Joined
Jul 5, 2014
Messages
19,069
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,171,654
Messages
5,876,720
Members
433,207
Latest member
Bitt0101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top