Hello,
I have the following code that makes users fill out cells (data validation lists) and would make them skip over cells if a certain criteria is not inputted:
The issue im having is that there is a conflict between the two dependancies (2.2 and 2.2.4). When a user clicks cell H21, they are able to make an input in cell H21, which should not be allowed since cell H16 (dependancy 2.2) is -25.
I hope this is clear enough, basically I would like to somehow combine both dependancies.
I have the following code that makes users fill out cells (data validation lists) and would make them skip over cells if a certain criteria is not inputted:
The issue im having is that there is a conflict between the two dependancies (2.2 and 2.2.4). When a user clicks cell H21, they are able to make an input in cell H21, which should not be allowed since cell H16 (dependancy 2.2) is -25.
I hope this is clear enough, basically I would like to somehow combine both dependancies.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'dependancy 2.2
Set Target = Target.Cells(1, 1)
Range("H17:H23").Interior.Color = RGB(217, 217, 217)
If Not Intersect(Target, Range("H16")) Is Nothing Then
If Target.Value = -25 Then
Range("H17:H23").Interior.Color = RGB(217, 217, 217)
Target.Offset(2, 0).Select
End If
End If
'dependancy 2.2.4
Set Target = Target.Cells(1, 1)
Range("H21").Interior.Color = RGB(217, 217, 217)
If Not Intersect(Target, Range("H20")) Is Nothing Then
If Target.Value = 0 Then
Range("H21").Interior.Color = RGB(217, 217, 217)
Target.Offset(2, 0).Select
End If
End If
End Sub
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'dependancy 2.2
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("H17:H23")) Is Nothing Then
If Range("H16").Value = -25 Then
Application.EnableEvents = False
Range("H25").Select
Application.EnableEvents = True
End If
End If
'dependancy 2.2.4
Set Target = Target.Cells(1, 1)
If Not Intersect(Target, Range("H21")) Is Nothing Then
If Range("H20").Value = 0 Then
Application.EnableEvents = False
Range("H22").Select
Application.EnableEvents = True
End If
End If
End Sub