Excel - If statements for skipping cells

mhasham

Board Regular
Joined
Feb 28, 2013
Messages
63
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.


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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Watch MrExcel Video

Forum statistics

Threads
1,109,048
Messages
5,526,477
Members
409,702
Latest member
thmoriarty

This Week's Hot Topics

Top