Hi all,
Can someone advice with tweaks with the code I already have .
I am trying to hide every column or in the row and show specific month using drop down .
Now the problem is it is not working properly when I say unhide “ALL”.
Also it is not working properly when change it another month .
.Private Sub Worksheet_Change(ByVal Target As Range) Dim cell As Range If Target.Row = 1 And Target.Column = 5 Then If Range("E1").Value = "Jan" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 1 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Feb" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 2 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Mar" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 3 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Apr" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 4 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "May" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 5 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Jun" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 6 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Jul" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 7 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Aug" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 8 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Sep" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 9 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Oct" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 10 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Nov" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 11 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "Dec" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) If Month(cell) = 12 Then Columns(cell.Column).Hidden = False Else Columns(cell.Column).Hidden = True End If Next cell ElseIf Range("E1").Value = "All" Then For Each cell In Range(Range("F4"), Range("F4").End(xlToRight)) Columns(cell.Column).Hidden = False Next cell End If End If End Sub
Bookmarks