VBA to hide columns where cell does not match another

[apinke1604]

Hi,

I have been trying to find an answer to this but can't find anything specific or that I can adapt to fit so hopefully someone will be able to help!

My data looks like this:

B C D E etc...
1
2 AP
3 AP KH TC
4 x x x
5 x x x
6 x x x
etc...

with B2 being a dropdown list that includes AP,KH,TC etc and TEAM. I would like all columns to show when TEAM is selected, and only the columns that contain the matching initials to show otherwise i.e. if KH is selected in B2 then only column D would be visible and the others would be hidden. Is there a way I can do this with VBA? Thanks in advance

 

[Takae]

Please copy to sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim C
    If Intersect(Target, Range("B2")) Is Nothing Then
        Exit Sub
    Else
        Cells.EntireColumn.Hidden = False
        Set rng = Range(Range("C3"), Cells(3, Columns.Count).End(xlToLeft))
        If Range("B2").Value = "TEAM" Then
            Cells.EntireColumn.Hidden = False
        Else
            For Each C In rng
                If C.Value <> Range("B2").Value Then
                    Columns(C.Column).EntireColumn.Hidden = True
                End If
            Next
        End If
    End If
End Sub

 

[apinke1604]

That's great Thank you Takae!