loop to stop and wait for cell value change.

I'm very new to this, but I have a simple macro that is working largely as intended. I want it to look at a cell, and give the user an error message if the cell value is less than or equal to a referenced cell. I want it to keep checking the cell, but I need to give the user an opportunity to change the value before each check. Can I set up some trigger that activates when the user enters data into the cell?

This is my code:

Sub proControlCheck()
    Range("B2").Select
    Do Until Selection.Value > "A2"
        If Selection.Value <= Range("A2") Then
        MsgBox "The High Control Value is less than the Low Control."
        Range("B2").Select
        End If
    Loop
End Sub

I appreciate any help.

Well, I've partially found my solution. I modified the code as follows:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If Target.Address = "$B$2" Then
    Range("B2").Select
        Do Until Selection.Value > "A2" Or Selection.Value = ""
            If Selection.Value <= Range("A2") Then
            MsgBox "The High Control Value is less than the Low Control."
            Selection.ClearContents
            End If
        Loop
 End If
End Sub

This works great as long as I keep entering values into cellB2 that cause the message box to appear. Once I enter a value that is greater than the value in cell A2, then spreadsheet accepts the value, then after a few seconds, Excel freezes.

Any ideas why?

HI

Try

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
 If Target.Address = "$B$2" Then
        Do Until Target.Value > Range("A2").Value Or Selection.Value = ""
            MsgBox "The High Control Value is less than the Low Control."
            Target.ClearContents
        Loop
 End If
End Sub

rylo

I do want the users to correct the value before they move on, so I tried your second code set. Unfortunately, it wouldn't allow me to enter a new value after it cleared the cell, because the cell was empty, which triggered the message box. I changed the code as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B2") >= Range("A2") Or Target.Value = "" Then Exit Sub
    Application.Undo
    
    Range("B2").Select
    MsgBox "The High Control Value is less than the Low Control."
    Application.EnableEvents = False
    Range("B2").ClearContents
    Application.EnableEvents = True
End Sub

Now it ends the macro if the cell is empty too. Unfortunately, it also allows the user to leave the cell blank, which isn't exactly desireable either. There are further operations in the sheet that will require values for both cells, so the omission would be caught later, but it would be nice to catch it in the beginning too.