values in column C must be larger than in B

[robertvdb]

I have a sheet for data entry in cols B and C. Values in col C must be larger than in B, I have the below code to check this.

But when I return to B, and I make a change there, I can still enter an invalid number. I could trap this through similar code for col B, but then I drive in circles...

Any suggestions ?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim col_B_rng As Range
Dim col_C_rng As Range

Set col_B_rng = Range("B2:B10")
Set col_C_rng = Range("C2:C10")

'check that value in col C is larger than in col B

If Not Application.Intersect(Target, col_B_rng) Is Nothing Then
    Target.Offset(0, 1).Select
End If

If Target.Count = 1 And Not Application.Intersect(Target, col_C_rng) Is Nothing Then
        If Target.Value <= Target.Offset(0, -1).Value Then
            Application.EnableEvents = False
            Target.Select
            MsgBox ("values in col C must be larger than in col B"), vbCritical
            Application.Undo
            Application.EnableEvents = True
        Else
            Target.Offset(1, -1).Select
        End If
End If

End Sub

 

[kevin9999]

Please try the following on a copy of your workbook:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B2:C10"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Cells(Target.Row, 2) >= Cells(Target.Row, 3) Then
            MsgBox "values in col C must be larger than in col B"
            Application.Undo
            Target.Select
        End If
        Application.EnableEvents = True
    End If
End Sub

 

[robertvdb]

Thanks Kevin; this works fine.

However, there still is an issue: when I put e.g. the value 4 in B2, and 5 in C2, then OK. But say I need 6 in B2 and 7 in C2. Then when I go back to B2 I have a problem.

 

[kevin9999]

Yes, you would need to put the value in column C first in such cases - unfortunately, I can't think of a workaround to that.

 

[robertvdb]

Thanks Kevin. I can't think of a gentle workaround either, however I've solved it in this way:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.CountLarge = 1 And Not Intersect(Range("C2:C10"), Target) Is Nothing Then
    Application.EnableEvents = False
    If Cells(Target.Row, 2) >= Cells(Target.Row, 3) Then
        MsgBox "values in col C must be larger than in col B"
        Application.Undo
        Target.Select
    Else
        Target.Offset(1, -1).Select
    End If
    Application.EnableEvents = True
End If
    
If Target.Cells.CountLarge = 1 And Not Intersect(Range("B2:B10"), Target) Is Nothing Then
    Application.EnableEvents = False
    If Cells(Target.Row, 2) >= Cells(Target.Row, 3) Then
        Cells(Target.Row, 3) = Cells(Target.Row, 2)
    End If
    Target.Offset(0, 1).Select
    Application.EnableEvents = True
End If

End Sub