Compare cell and msg
 

Hi All,

I need some help woth code.
On cell B2 when focus is lost then compare its value with the value of A2,
if b2 value is less then A2 then I want to popup a message saying value in B2
cannot be less then A2 and bring the focus back on B2

Thank In Advance
AM

Compare cell and msg
 

Hi AM.

You can achieve the stated objective
without code; try using Excel's Data
Validation tool:


Menu | Data | Validation



---
Regards.
Norman

Compare cell and msg
 

However, if your project needs to use code. Try the macro below. If needs
to be pasted into the worksheet code window. Right click the sheet tab and
select View Code from the drop down menu. Copy this code and paste it into
the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Then
If Range("B2") < Range("A2") Then
MsgBox "CELL B2 CANNOT BE LESS THAN CELL A2, TRY AGAIN"
Range("B2").Select
End If
End If
End Sub




"AM" wrote:

Hi All,

I need some help woth code.
On cell B2 when focus is lost then compare its value with the value of A2,
if b2 value is less then A2 then I want to popup a message saying value in B2
cannot be less then A2 and bring the focus back on B2

Thank In Advance
AM

Compare cell and msg
 

Thanks, This is exactly what I needed and it works. Appreciate everyones help.

AM

"JLGWhiz" wrote:

However, if your project needs to use code. Try the macro below. If needs
to be pasted into the worksheet code window. Right click the sheet tab and
select View Code from the drop down menu. Copy this code and paste it into
the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Then
If Range("B2") < Range("A2") Then
MsgBox "CELL B2 CANNOT BE LESS THAN CELL A2, TRY AGAIN"
Range("B2").Select
End If
End If
End Sub




"AM" wrote:

Hi All,

I need some help woth code.
On cell B2 when focus is lost then compare its value with the value of A2,
if b2 value is less then A2 then I want to popup a message saying value in B2
cannot be less then A2 and bring the focus back on B2

Thank In Advance
AM

Compare cell and msg
 

Norman,

Thanks for your response, I am not sure how I can compare value in 2 cell
and give message using validation? Can you please give little mroe details?

Thanks
AM

"Norman Jones" wrote:

Hi AM.

You can achieve the stated objective
without code; try using Excel's Data
Validation tool:


Menu | Data | Validation



---
Regards.
Norman

Compare cell and msg
 

Hi JLGWhiz,

To avoid a Run-time 13 error if the value
of a multi-cell range is changed, more
robust might be the following version:

'=========
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Rng2 As Range

With Me
Set Rng = .Range("A2")
Set Rng2 = .Range("B2")
End With

If Not Intersect(Rng2, Target) Is Nothing Then
If Rng2.Value < Rng.Value Then
MsgBox "CELL B2 CANNOT BE LESS " _
& "THAN CELL A2, TRY AGAIN"
Rng2.Select
End If
End If
End Sub
'<<=========



---
Regards.
Norman

"JLGWhiz" wrote in message
...
However, if your project needs to use code. Try the macro below. If
needs
to be pasted into the worksheet code window. Right click the sheet tab
and
select View Code from the drop down menu. Copy this code and paste it
into
the code window.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("B2") Then
If Range("B2") < Range("A2") Then
MsgBox "CELL B2 CANNOT BE LESS THAN CELL A2, TRY AGAIN"
Range("B2").Select
End If
End If
End Sub




"AM" wrote:

Hi All,

I need some help woth code.
On cell B2 when focus is lost then compare its value with the value of
A2,
if b2 value is less then A2 then I want to popup a message saying value
in B2
cannot be less then A2 and bring the focus back on B2

Thank In Advance
AM

Compare cell and msg
 

And, to be consistent with my use of
an object variable for B2, it would be
better to replace:

MsgBox "CELL B2 CANNOT BE LESS " _
& "THAN CELL A2, TRY AGAIN"

with
MsgBox Prompt:="CELL " & Rng2.Address(0, 0) _
& " CANNOT BE LESS " _
& "THAN CELL A2, TRY AGAIN"

---
Regards.
Norman


"Norman Jones" wrote in message
...
Hi JLGWhiz,

To avoid a Run-time 13 error if the value
of a multi-cell range is changed, more
robust might be the following version:

'=========
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range
Dim Rng2 As Range

With Me
Set Rng = .Range("A2")
Set Rng2 = .Range("B2")
End With

If Not Intersect(Rng2, Target) Is Nothing Then
If Rng2.Value < Rng.Value Then
MsgBox "CELL B2 CANNOT BE LESS " _
& "THAN CELL A2, TRY AGAIN"
Rng2.Select
End If
End If
End Sub
'<<=========



---
Regards.
Norman