Macro to check what was just typed in.

[tonywatsonhelp]

Hi Everyone,
I'm not sure how t do this but I want a way to check what has just been typed it

I have a range "AI9:AI10000"

And "AH9:AH10000"

Id like a macro that when some type anything into a cell of "AI9:AI10000" it checks to see if it Higher or equal to the value in cell "AH9:AH10000"
if it is great, if not message box "The value must be higher than the sale price!, Please try again, and clear it and select it.

please help with this if you can
Thanks
Tony

 

[Osvaldo Palmeiro]

Hi, Tony.
Please, try a copy of this code in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Count > 1 Then Exit Sub
 If Intersect(Target, [AI9:AI10000]) Is Nothing Or Target.Value = "" Then Exit Sub
 If Target.Value < Target.Offset(, -1).Value Then
  MsgBox "The value must be higher than the sale price!, Please try again"
  Target.Value = ""
 End If
End Sub

 

[rollis13]

Hi to all.
@Osvaldo Palmeiro, I would add these other two lines of code to avoid redundante triggering of the event Worksheet_Change, here:

Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True

 

[tonywatsonhelp]

Thank you to both of you,
works a treat
Tony

 

[Osvaldo Palmeiro]

Hi to all.
@Osvaldo Palmeiro, I would add these other two lines of code to avoid redundante triggering of the event Worksheet_Change, here:

Application.EnableEvents = False
Target.Value = ""
Application.EnableEvents = True

Well, I think it's not necessary since if Target is empty then the execution will be terminated. ~~~> Or Target.Value = "" Then Exit Sub
I prefer this way as it eliminates the risk that if the execution is interrupted for whatever reason, not all users have knowledge enough to reset EnableEvents.

 

[rollis13]

Well, I think it's not necessary since if Target is empty then the execution will be terminated.

Event Worksheet_Change triggers whatever change is made in a cell, even if emptied like in this case.

I prefer this way as it eliminates the risk that if the execution is interrupted for whatever reason, not all users have knowledge enough to reset EnableEvents.

This instead is a reason that justifies a couple of extra cycles .