[VBA]Private Sub Worksheet_Change(ByVal Target As Range)

Me.Unprotect "O1"

'Application.ScreenUpdating = False

If Not Intersect(Target, Me.Range("D9")) Is Nothing Then

If Target.Value = "" Then

Call RowVisibility(Me.Range("21:22,24:150"), True, True)

ElseIf Target.Value = "OFGEN User Access Removal (stop user access to OFGEN)" Then

Call RowVisibility(Me.Range("21:22,24:150"), False, True)

ElseIf Target.Value = "CLONE Existing OFGEN User" Then

Call RowVisibility(Me.Range("21:22,24:150,34:150"), True, False, True)

End If

ElseIf Not Intersect(Target, Me.Range("I38")) Is Nothing Then

'For Role#1
If Target.Value = 0 Then

Call RowVisibility(Me.Range("24:35,34:35,46:137,138:150"), True, False, True, False)

'For Role#2
ElseIf Target.Value = 1 Then

Call RowVisibility(Me.Range("24:33,34:55,56:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I48")) Is Nothing Then

'For Role#3
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:33,34:65,66:137,138:150"), True, False, True, False)

'For Role#3
ElseIf Target.Value = 1 Then

Call RowVisibility(Me.Range("24:33,33:65,66:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I59")) Is Nothing Then

'For Role#4
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:76,77:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I70")) Is Nothing Then

'For Role#5
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:86,87:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I80")) Is Nothing Then

'For Role#6
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:96,97:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I90")) Is Nothing Then

'For Role#7
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35106,107:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I100")) Is Nothing Then

'For Role#8
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:116,117:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I110")) Is Nothing Then

'For Role#9
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:126,127:137,138:150"), True, False, True, False)
End If

ElseIf Not Intersect(Target, Me.Range("I120")) Is Nothing Then

'For Role#10
If Target.Value = 1 Then

Call RowVisibility(Me.Range("24:34,35:126,127:137,138:150"), True, False, False, False)
End If

End If

Me.Protect "O1"
End Sub


Private Function RowVisibility(ByRef rng As Range, ParamArray visibility())
Dim rngArea As Range
Dim idx As Long

For Each rngArea In rng.Areas
rngArea.EntireRow.Hidden = visibility(idx)
idx = idx + 1
Next rngArea

End Function[/VBA]