In the sub below, I pointed to the relevant line.
The rest of the code below is just a suggestion on how to make your code easier to read ,write, and maintain.
These Constants should be in a Standard Module so they can be used in any code anywhere in the Workbook.
Option Explicit
Const Role1HiddenRange As String = "19:21, 23:23, 25:25, 27:28, 30:30, 32:33"
Const Role1VisibleRange As String = "18:18, 22:22, 24:24, 26:26, 29:29, 31:31"
Const Role2HiddenRange As String = "19:21, 23:23, 24:28, 30:33"
Const Role2VisibleRange As String = "18:18, 22:22, 29:29"
This is how I would use them in your sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim HideRange As String
Dim ShowRange As String
If Not Target.Address = "$E$14" Then Exit Sub '<-----------
Select Case Target.Value
Case "Role1"
HideRange = Role1HiddenRange
ShowRange = Role1VisibleRange
Case "Role2"
HideRange = Role2HiddenRange
ShowRange = Role2VisibleRange
Case Else: Exit Sub
End Select
Range(HideRange).EntireRow.Hidden = True
Range(ShowRange).EntireRow.Hidden = False
End Sub
Notice that the Case codes are identical, except for the numbers? Write the first one, then copy and paste it as many times as you have "Roles." Then just change the numbers in each line. I R such a LAzy Typise
BUT: This is the way I would write a Worksheet Event Sub, This way I can write code for the same Event with different outcomes
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$14" Then HideShow Target
If Target.Address = "$D$14" Then OtherSub Target
End Sub
Sub HideShow(Target)
Dim HideRange As String
Dim ShowRange As String
' Edit: Not needed in this sub: If Not Target.Address = "$E$14" Then Exit Sub
Select Case Target.Value
Case "Role1"
HideRange = Role1HiddenRange
ShowRange = Role1VisibleRange
Case "Role2"
HideRange = Role2HiddenRange
ShowRange = Role2VisibleRange
Case Else: Exit Sub
End Select
Range(HideRange).EntireRow.Hidden = True
Range(ShowRange).EntireRow.Hidden = False
End Sub