Sub separation issues

[OversizedCranium]

Hello,

I am very new to VBA so this is probably a quick fix, but I can't seem to get a second command to work underneath the first....

Is anyone able to help?

The first IF works perfectly, however I'm struggling to add another below it.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("C18"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "Residential": Rows("33:45").EntireRow.Hidden = True
                             Rows("19:32").EntireRow.Hidden = False
    Case Is = "Education": Rows("19:32").EntireRow.Hidden = True
                           Rows("33:37").EntireRow.Hidden = False
                           Rows("38:45").EntireRow.Hidden = True
    Case Is = "Res & Ed": Rows("19:37").EntireRow.Hidden = False
                          Rows("38:45").EntireRow.Hidden = True
    Case Is = "Part-Time": Rows("19:37").EntireRow.Hidden = True
                           Rows("38:45").EntireRow.Hidden = False
    Case Is = "None Required": Rows("19:45").EntireRow.Hidden = True
    
    End Select
    End If
    
  
End Sub

Private Sub Worksheet_Change_Bel(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("C52"), Range(Target.Address)) Is Nothing Then
    Select Case Target.Value
    Case Is = "No": Rows("53:55").EntireRow.Hidden = True
    Case Is = "Yes": Rows("53:55").EntireRow.Hidden = False
    
    End Select
    End If
    
    End Sub

Thanks

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "C18" Then
    Select Case Target.Value
    Case Is = "Residential": Rows("33:45").EntireRow.Hidden = True
                             Rows("19:32").EntireRow.Hidden = False
    Case Is = "Education": Rows("19:32").EntireRow.Hidden = True
                           Rows("33:37").EntireRow.Hidden = False
                           Rows("38:45").EntireRow.Hidden = True
    Case Is = "Res & Ed": Rows("19:37").EntireRow.Hidden = False
                          Rows("38:45").EntireRow.Hidden = True
    Case Is = "Part-Time": Rows("19:37").EntireRow.Hidden = True
                           Rows("38:45").EntireRow.Hidden = False
    Case Is = "None Required": Rows("19:45").EntireRow.Hidden = True
    
    End Select
ElseIf Target.Address(0, 0) = "C52" Then
    Select Case Target.Value
    Case Is = "No": Rows("53:55").EntireRow.Hidden = True
    Case Is = "Yes": Rows("53:55").EntireRow.Hidden = False
    
    End Select
    
    End If
    
  
End Sub

 

[Joe4]

Everything must be inside of a single "Worksheet_Change" event procedure, and you cannot take any liberties in renaming it if you want it to work automatically.

Also,
Range(Target.Address)
is redundant and totally unnecessary. "Target" is already a range variable.

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("C18"), Target) Is Nothing Then
    Select Case Target.Value
        Case Is = "Residential": Rows("33:45").EntireRow.Hidden = True
                                 Rows("19:32").EntireRow.Hidden = False
        Case Is = "Education": Rows("19:32").EntireRow.Hidden = True
                               Rows("33:37").EntireRow.Hidden = False
                               Rows("38:45").EntireRow.Hidden = True
        Case Is = "Res & Ed": Rows("19:37").EntireRow.Hidden = False
                              Rows("38:45").EntireRow.Hidden = True
        Case Is = "Part-Time": Rows("19:37").EntireRow.Hidden = True
                               Rows("38:45").EntireRow.Hidden = False
        Case Is = "None Required": Rows("19:45").EntireRow.Hidden = True
    End Select
End If
    
If Not Application.Intersect(Range("C52"), Target) Is Nothing Then
    Select Case Target.Value
        Case Is = "No": Rows("53:55").EntireRow.Hidden = True
        Case Is = "Yes": Rows("53:55").EntireRow.Hidden = False
    End Select
End If
    
End Sub

 

[OversizedCranium]

Awesome,

Works perfect,

going forward with this logic, would I apply Elseif again for further adjustments?

 

[Fluff]

That's right.

 

[OversizedCranium]

That's right.

Ah brilliant,

Is formatting important?

Thanks for the help!

 

[Fluff]

Is formatting important?

In what way?

 

[OversizedCranium]

In what way?

In where the wording is aligned?

Sorry if that's a stupid question, but I tried to replicate it for another parameter, didn't seem to work, copied yours and changed it to fit below and it suddenly worked

 

[Fluff]

You don't need to format the code with indents, but I always do as I find it easier to see the general flow of the code.

 

[OversizedCranium]

You don't need to format the code with indents, but I always do as I find it easier to see the general flow of the code.

Okay, Thanks for your help! Much appreciated