Hide column based on cell value

[olympiac]

I am using the code below to hide a column based on a cell value.
I need to repeat this macro from column C to column AF.
How do I need to modify it to avoid copying this code for each colum?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("C5").Value = 0 Then
Columns("C").EntireColumn.Hidden = True
Else
Columns("C").EntireColumn.Hidden = False
End If
End Sub


Thanks

 

[JackDanIce]

n/a

 

[Sektor]

I guess you need Worksheet_Change instead of Worksheet_SelectionChanged. Correct?

Private Sub [B][COLOR="Red"]Worksheet_Change[/COLOR][/B](ByVal Target As Range)
    If Range("C5").Value = 0 Then
        Columns("C:AF").EntireColumn.Hidden = True
    Else
        Columns("C:AF").EntireColumn.Hidden = False
    End If
End Sub

 

[olympiac]

Thanks for the answer.
The code works but it hides all the columns in the spreadsheet and not only the columns from C to AF.
Is there a way to limit it only for the range C:AF ?

 

[Sektor]

Did you try my code?

 

[olympiac]

It works perfecly. Thanks

 

[Sektor]

You're welcome!

 

[Sektor]

Or this way...

Private Sub Worksheet_Change(ByVal Target As Range)
    Columns("C:AF").EntireColumn.Hidden = Range("C5").Value = 0
End Sub

 

[olympiac]

Mmmm! Not really because the column must be hidden if the value in the related column is 0.
I mean...
if C5 = 0 the Column C must be hidden
if D5 = 0 the Column D must be hidden
etc until column AF

Any thought?

 

[Sektor]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    For i = 3 To 32
        Cells(1, i).EntireColumn.Hidden = Cells(5, i) = 0
    Next
End Sub

Or this...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Integer
    For i = 3 To 32: Cells(1, i).EntireColumn.Hidden = Cells(5, i) = 0: Next
End Sub