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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,109
Messages
5,857,439
Members
431,879
Latest member
KiwDaWabbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top