Compare cell and msg

[AM]

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

 

[Norman Jones]

Hi AM.

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


Menu | Data | Validation

 

[JLGWhiz]

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]

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

AM

 

[AM]

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]

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
'<<=========

 

[Norman Jones]

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"