COMPARING TWO COLUMNS AND DISPLAYING MSGBOX

[naga rajan]

Hello everyone,

Am having two values in A1 and B1.
The cell C1 has one value.
I need a msgbox to pop up if either A1 or B1 greater than C1.
Please help.

 

[lhkittle]

Hello everyone,



Am having two values in A1 and B1.

The cell C1 has one value.

I need a msgbox to pop up if either A1 or B1 greater than C1.

Please help.

Hi naga rajan,

Try this in the worksheet code module.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value Or Range("B1").Value > Range("C1") Then
MsgBox "A1 or B1 is greater than C1", vbOKOnly, "High Hopes"
End If
End Sub

HTH
Regards,
Howard

 

[joeu2004]

I need a msgbox to pop up if either A1 or B1 greater than C1.

[....]
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value Or Range("B1").Value > Range("C1") Then
MsgBox "A1 or B1 is greater than C1", vbOKOnly, "High Hopes"
End If
End Sub

Correction:

If Range("A1") > Range("C1") Or Range("B1") > Range("C1") Then

Also note that the Worksheet_Change event macro goes into a worksheet
module, not a normal module. Right-click on the worksheet tab to open the
worksheet module in the VBA window.

 

[lhkittle]

naga rajan wrote:

I need a msgbox to pop up if either A1 or B1 greater than C1.

[....]

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value Or Range("B1").Value > Range("C1") Then

MsgBox "A1 or B1 is greater than C1", vbOKOnly, "High Hopes"

End If

End Sub

Correction:



If Range("A1") > Range("C1") Or Range("B1") > Range("C1") Then



Also note that the Worksheet_Change event macro goes into a worksheet

module, not a normal module. Right-click on the worksheet tab to open the

worksheet module in the VBA window.

Whoa, good catch Joeu2004! It's not clear to me why my line produces the msgbox when both A1 & B1 are LESS than C1. And I tested my code, but missed that somehow...???

Howard

 

[joeu2004]

[....]
It's not clear to me why my line produces the msgbox when
both A1 & B1 are LESS than C1.

Because A1 <> 0 (presumably).

Your if-statement reads: "if A1 is true or if B1 > C1, then ....".

Any non-zero value is considered "true". So "if A1 is true" is false only
when A1 = 0.

Since "A1 is true" is (almost) always true, the true/false value of the 2nd
part (B1 > C1) does not matter. "TRUE OR anything" is true.

 

[lhkittle]

If Range("A1").Value Or Range("B1").Value > Range("C1") Then

[....]

It's not clear to me why my line produces the msgbox when

both A1 & B1 are LESS than C1.

Because A1 <> 0 (presumably).



Your if-statement reads: "if A1 is true or if B1 > C1, then ....".



Any non-zero value is considered "true". So "if A1 is true" is false only

when A1 = 0.



Since "A1 is true" is (almost) always true, the true/false value of the 2nd

part (B1 > C1) does not matter. "TRUE OR anything" is true.

Thanks, good stuff to digest for lurkers like me.

Howard

 

[naga rajan]

Hello,

Thanks for the help.
When I define the range to A1:A100 > C1 its giving mismatch error.
I want to display a msgbox when the value of A1:A100 or B1:B100 > C1.
Please help.