Pop up warning if cell is greater than another cell

Zimmerman

Well-known Member
Joined
Jan 22, 2009
Messages
663
Does anybody know a VBA code for a pop up meassage when a cell is greater than another? I have in cell AW59 a sum of AQ59:AV59 and if this number is greater than cell AN59 I'd like to have a pop up warning message saying you don't have enough inventory to do this.

Thanks
Windows XP
Excel 2003
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Yes I could but i couldn't figure that one out because the user wont type in cell AW59 and what i have is cell AN59 could be a negative number like -200 so i couldn't figure out how to put a data validation in on cell AW59 saying that if it's greater than than AN59 to give me a warning.

And sorry i forgot that cell AN59 is also a sum of AH59:AM59 so this number will be changing as they type numbers in. The cell AW59 actually sums up AQ59:AV59 which it also subtracts out from cells AH59:AM59.

So if cell ah59 has 600 (which is Monday) and cell au59 has 500 (which is Friday) Cell An59 will have 600 in it until Friday they enter the 500 and then it will now have a total of 100 in it. What i'm trying to do is once cell an59 goes negative a warning will pop up telling them they dont have the inventory to do this.

Does that make sense
 
Upvote 0
Thanks for being patient with me because now I'm a little more confused. If you could still help below is what i have.

Cell AN59 =((AH59-AQ59)+(AI59-AR59)+(AJ59-AS59)+(AK59-AT59)+(AL59-AU59)+(AM59-AV59))

Cell AW59 =((AQ59-(AZ59+BA59))+(AR59-(BB59+BC59))+(AS59-(BD59+BE59))+(AT59-(BF59+BG59))+(AU59-(BH59+BI59))+(AV59-(BJ59+BK59)))

So you see i can't figure out how or where to put the data validation or what validation to use.

Thanks again for all the help
 
Upvote 0
Right click your worksheet -> View Code. In the first dropdown, choose 'Worksheet'. In the second dropdown, choose 'Change'. Then Paste this code. It will popup a MsgBox when the contents of AW59 exceed AN59 and delete the user entry which caused this popup.


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("AW59").Value > Range("AN59").Value Then
MsgBox ("Not enough inventory!")
Target.Value = ""
End If

End Sub
 
Upvote 0
Absolutely perfect. Thank youvery much.

One more question though.

I would like to do this in 100 or more cells, would i just keep copying the same code and pasting it under the next one?
 
Upvote 0
Yes, so it will be for the cells in row AW, cells 59 thru 88 then for cells AW94 thru aw103. It will keep going like this this for 100 or so cells in the same column butthey all wont be in the same row sequence. I skip some rows.
 
Upvote 0
You don't have to copy paste for each row. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
Dim rngAW As Range, rngAN As Range
Set rngAW = Application.Intersect(Target.EntireRow, Range("AW1").EntireColumn)
Set rngAN = Application.Intersect(Target.EntireRow, Range("AN1").EntireColumn)
If rngAW.Value > rngAN.Value Then
MsgBox ("Not enough inventory!")
Target.Value = ""
End If
End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top