2 VBA Codes in the same workbook, 1 not working

[JQB87]

Hello,

I have a workbook with 3 sheets. Someone on here helped me get a VBA code set up for the first sheet. Sheet 1's code works perfectly and exactly what I want. I want to use that same formatting on the 2nd sheet but for some reason the when I change what the VBA code is looking at on the 2nd sheet, it doesn't appear to be working in the same manner as it is on the first sheet, not working at all actually. Not sure what I'm doing wrong, pretty much brand new to VBA Code. Any help is appreciated! Still new to these forums as well so if I didn't post right, sorry!

Sheet 1
VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 3 Then Exit Sub
   Select Case Target.Address(0, 0)
      Case "C4"
         Rows("5:20").Hidden = Not Target.Value = "Y"
      Case "C21"
         Rows("22:35").Hidden = Not Target.Value = "Y"
         Case "C36"
         Rows("37:49").Hidden = Not Target.Value = "Y"
         Case "C50"
         Rows("51:60").Hidden = Not Target.Value = "Y"
         Case "C61"
         Rows("62:80").Hidden = Not Target.Value = "Y"
         Case "C81"
         Rows("82:99").Hidden = Not Target.Value = "Y"
         Case "C100"
         Rows("101:110").Hidden = Not Target.Value = "Y"
         Case "C111"
         Rows("112:119").Hidden = Not Target.Value = "Y"
         Case "C120"
         Rows("121:127").Hidden = Not Target.Value = "Y"
         Case "C128"
         Rows("129:139").Hidden = Not Target.Value = "Y"
         Case "C140"
         Rows("141").Hidden = Not Target.Value = "Y"
         Case "C142"
         Rows("143:151").Hidden = Not Target.Value = "Y"
   End Select
End Sub

Sheet 2
VBA Code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 3 Then Exit Sub
   Select Case Target.Address(0, 0)
      Case "B5"
         Rows("14:70").Hidden = Not Target.Value = "Y"
    Case "B6"
         Rows("7:8").Hidden = Not Target.Value = "Y"
   End Select
End Sub

 

[My Aswer Is This]

In your first script you want script to only work for column 3
and you use: Case "C100"
C100 is in column 3
Which works.
But in your second script you want script to only work for column 3
But use use: Case "B5"
B5 is not in column 3

 

[JLGWhiz]

If Target.Column <> 3 Then Exit Sub

Change to

If Target.Column <> 2 Then Exit Sub

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("B5:B6:C:C")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 2
            Select Case Target.Address
                Case "$B$5"
                    Rows("14:70").Hidden = Not Target.Value = "Y"
                Case "$B$6"
                    Rows("7:8").Hidden = Not Target.Value = "Y"
            End Select
        Case Is = 3
            Select Case Target.Address
                Case "$C$4"
                    Rows("5:20").Hidden = Not Target.Value = "Y"
                Case "$C$21"
                    Rows("22:35").Hidden = Not Target.Value = "Y"
                Case "$C$36"
                    Rows("37:49").Hidden = Not Target.Value = "Y"
                Case "$C$50"
                    Rows("51:60").Hidden = Not Target.Value = "Y"
                Case "$C$61"
                    Rows("62:80").Hidden = Not Target.Value = "Y"
                Case "$C$81"
                    Rows("82:99").Hidden = Not Target.Value = "Y"
                Case "$C$100"
                    Rows("101:110").Hidden = Not Target.Value = "Y"
                Case "$C$111"
                    Rows("112:119").Hidden = Not Target.Value = "Y"
                Case "$C$120"
                    Rows("121:127").Hidden = Not Target.Value = "Y"
                Case "$C$128"
                    Rows("129:139").Hidden = Not Target.Value = "Y"
                Case "$C$140"
                    Rows("141").Hidden = Not Target.Value = "Y"
                Case "$C$142"
                    Rows("143:151").Hidden = Not Target.Value = "Y"
            End Select
    End Select
End Sub

 

[JQB87]

Oh wow thank you that worked!

 

[JQB87]

Thank you all for your responses! Wouldn't have figured that out otherwise.

 

[mumps]

You are very welcome.