If true, hide Range("P:U") & Range ("V:AA")

[jelle1100]

Private Sub Worksheet_Change(ByVal Target As Range)    If Not Intersect(Range("H2"), Target) Is Nothing Then
       
        If Worksheets("2016!").Range("h2") = "SIS" Then
        Range("P:U").EntireColumn.Hidden = True
        Range("V:AA").EntireColomn.Hidden = False 'this line gives a runtime error 438


        End If


    End If
End Sub

There is a drop-down list in h2 with three options. "SIS" should hide the first range, but show the second range. Than "AGP" should show the first range, but hide the second range and "AA" should show both ranges. Can someone help me with this?

 

[Rick Rothstein]

Private Sub Worksheet_Change(ByVal Target As Range)    If Not Intersect(Range("H2"), Target) Is Nothing Then
       
        If Worksheets("2016!").Range("h2") = "SIS" Then
        Range("P:U").EntireColumn.Hidden = True
        Range("V:AA").Entire[B][COLOR="#FF0000"]Colomn[/COLOR][/B].Hidden = False 'this line gives a runtime error 438


        End If


    End If
End Sub

You spelled Column wrong (see red highlighted text).

There is a drop-down list in h2 with three options. "SIS" should hide the first range, but show the second range. Than "AGP" should show the first range, but hide the second range and "AA" should show both ranges. Can someone help me with this?

What is the third option and what should happen for it?

 

[WarPigl3t]

Columns("22:27").Hidden = False
End If

 

[Peter_SSs]

What is the third option and what should happen for it?

Rick, I think these are the 3 options and desired results.

There is a drop-down list in h2 with three options. "SIS" should hide the first range, but show the second range. Than "AGP" should show the first range, but hide the second range and "AA" should show both ranges.

 

[jelle1100]

thank you! It works fine now. Also with the other lines added

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("H2"), Target) Is Nothing Then


        If Worksheets("2016!").Range("h2") = "SIS" Then
        Range("P:U").EntireColumn.Hidden = False
        Range("V:AA").EntireColumn.Hidden = True
        Else


            If Worksheets("2016!").Range("h2") = "AGP" Then
            Range("P:U").EntireColumn.Hidden = True
            Range("V:AA").EntireColumn.Hidden = False
            Else


                If Worksheets("2016!").Range("h2") = "AA" Then
                Range("P:U").EntireColumn.Hidden = False
                Range("V:AA").EntireColumn.Hidden = False
                Else


                    If Worksheets("2016!").Range("h2") = "AGP & SIS" Then
                    Range("P:U").EntireColumn.Hidden = False
                    Range("V:AA").EntireColumn.Hidden = False








                    End If
                End If
            End If
        End If
    End If


    End Sub

 

[Rick Rothstein]

Rick, I think these are the 3 options and desired results.

I read completely over that.

 

[Rick Rothstein]

Assuming the worksheet whose Change event you are monitoring is 2016!, then this code should work...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address(0, 0) = "H2" Then
    Range("P:U,V:AA").EntireColumn.Hidden = True
    If Target <> "AA" Then
      Columns("P:U").Hidden = Not Target = "SIS"
      Columns("V:AA").Hidden = Not Target = "AG"
    End If
  End If
End Sub