AutoHide VBA code

[gustaphson]

Good Afternoon,

I am completing a workbook in which I need to hide rows based on a selection in the first sheet. The following code is included in the first sheet "S1" and it works. Sheet "S2" is identical to "S1" except for the fact that cell C6 on "S2" simply refrences C6 on "S1", and C6 on "S1" is a dropdown. The code on "S2" does not work, can anyone point me in the right direction? Thanks in advance!

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("C6"), Range(Target.Address)) Is Nothing Then
        Select Case Target.Value
        Case Is = "6.33": Rows("21").EntireRow.Hidden = False
        Case Is = "9.5": Rows("21").EntireRow.Hidden = True
        Case Is = "12.5": Rows("21").EntireRow.Hidden = True
        Case Is = "19": Rows("21").EntireRow.Hidden = True
        Case Is = "25": Rows("21").EntireRow.Hidden = True
        Case Is = "37.5": Rows("21").EntireRow.Hidden = True
        Case Is = "SMA95": Rows("21").EntireRow.Hidden = True
        Case Is = "SMA125": Rows("21").EntireRow.Hidden = True
        Case Is = "4.75": Rows("21").EntireRow.Hidden = True
                                                  
       
        End Select
End If
End Sub

 

[Fluff]

Are you trying to hide row 21 on both sheets?

 

[gustaphson]

Are you trying to hide row 21 on both sheets?

Yes.

 

[Fluff]

Ok how about this. It needs to go in the S1 sheet module in place of the existing code.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Range("C6"), Target) Is Nothing Then
      Select Case Target.Value
      Case "6.33"
         Rows(21).Hidden = False
         Sheets("S2").Rows(21).Hidden = False
      Case Else
         Rows(21).Hidden = True
         Sheets("S2").Rows(21).Hidden = True
      End Select
   End If
End Sub

 

[gustaphson]

Ok how about this. It needs to go in the S1 sheet module in place of the existing code.

Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Range("C6"), Target) Is Nothing Then
      Select Case Target.Value
      Case "6.33"
         Rows(21).Hidden = False
         Sheets("S2").Rows(21).Hidden = False
      Case Else
         Rows(21).Hidden = True
         Sheets("S2").Rows(21).Hidden = True
      End Select
   End If
End Sub

That worked. Thanks!

 

[Fluff]

You're welcome & thanks for the feedback.