Pop up warning if cell is greater than another cell

[Zimmerman]

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

 

[Zimmerman]

That will work. Thank you very much!

 

[Sakshi0206]

Hi I am creating a similar macro but i am stuck. I have values in cells C51:CH51 which i want to compare to the values in cell C54:CH54 and then want a message box to pop up. I am using:

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.EntireColumn, Range("C51").EntireRow)
Set rngAN = Application.Intersect(Target.EntireColumn, Range("C54").EntireRow)
If rngAW.Value > rngAN.Value Then
MsgBox "Not enough cash. Maximum loans which can be accepted is " & Application.Intersect(Target.EntireColumn, Range("C54").EntireRow).Value
Target.Value = ""
End If
End If
End Sub

It was working fine till now, But now i am trying to apply a dropdown in Cell C11 and it is giving me the msg box.

Can anyone help?

 

[Roderick_E]

Worksheet_Change triggers on ANY change to the worksheet. Think about what you want to actually trigger the msgbox.
I suspect the user is entering data into C54:CH54 so why not only trigger if instr(target.address,"$54")

 

[Sakshi0206]

I have values in row 54 and when you type value in row 51 the msg box will appear if the value in 51 is more than value in 54

 

[Roderick_E]

ok so the user is inputting data into row 51. Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If instr(Target.address,"$51") Then
Dim rngAW As Range, rngAN As Range
Set rngAW = Application.Intersect(Target.EntireColumn, Range("C51").EntireRow)
Set rngAN = Application.Intersect(Target.EntireColumn, Range("C54").EntireRow)
If rngAW.Value > rngAN.Value Then
MsgBox "Not enough cash. Maximum loans which can be accepted is " & Application.Intersect(Target.EntireColumn, Range("C54").EntireRow).Value
Target.Value = ""
End If
End If
End Sub

 

[Sakshi0206]

Hi Roderick,

Thanks a lot it is working properly now. Thanks a lot. Seriously thanks a lot.

 

[Sakshi0206]

I need one more help.

I need to apply a macro on a drop down on the same sheet. How can i do that? I have to recalculate the file once the value is changed in the drop down and then call few macros i have created on different sheets. I have tried creating a macro but it stops after recalculating the file. It is not running other macros i have called there.

Private Sub Worksheet_CurrencyChange(ByVal Target As Range)


If Not Intersect(Target, Range("c11")) Is Nothing Then
Call Macro1
Call CurrencyPL
Call CurrencyCFS
Call CurrencyBS
Call CurrencyLoans
Call CurrencyLoans1
End If


End Sub

Macro 1 is to refresh the file. all other are specific macros created for different sheets.

Can anyone help?

 

[dc209]

I am trying to get an inequality to display a pop up message and even worse I have no experience with VBAC. Essentially I was a message box to appear if the value in "L27" exceeds a set value of "86.00". L27 is referenced to "E18" which contains "=SUM(E4:E17)" So what I have done is try to follow this thread as closely as I can and I have written the following code:

Private Sub Worksheet_Change(ByVal Target As Range)


If Range(L27).Value > Target.Value Then
MsgBox ("Total hours exceed 86 hours. Check time data and/or move excess time to Comp")
Target.Value = "86"


End Sub

Any suggestions? What have I done wrong?