Hide/Unhide Columns Based on Date Range in Data Validation List Selection

[MikaChu]

Hi,

I'm trying to work on a macro to hide and unhide columns based on the date that was selected on a data validation list.

My date columns are from "F" to "GG", and my data validation list is on "B9".

I researched on some macros but to no avail.

Here's what my current macro looks like:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Index > 4 Then Exit Sub
If Target.Address(False, False) = "B9" Then
Application.ScreenUpdating = False
Columns("F:GG").Hidden = False
Select Case Target.Value
Case "11/1/2013"
Range("F:BI").EntireColumn.Hidden = False
Range("AT:CW").EntireC olumn.Hidden = True
Range("BZ:EC").EntireColumn.Hidden = True
Range("DF:FI").EntireColumn.Hidden = True
Range("EL:GG").EntireColumn.Hidden = True
Case "12/1/2013"
Range("F:BI").EntireColumn.Hidden = True
Range("AT:CW").EntireColumn.Hidden = False
Range("BZ:EC").EntireColumn.Hidden = True
Range("DF:FI").EntireColumn.Hidden = True
Range("EL:GG").EntireColumn.Hidden = True
Case "1/1/2014"
Range("F:BI").EntireColumn.Hidden = True
Range("AT:CW").EntireColumn.Hidden = True
Range("BZ:EC").EntireColumn.Hidden = False
Range("DF:FI").EntireColumn.Hidden = True
Range("EL:GG").EntireColumn.Hidden = True
Case "2/1/2014"
Range("F:BI").EntireColumn.Hidden = True
Range("AT:CW").EntireColumn.Hidden = True
Range("BZ:EC").EntireColumn.Hidden = True
Range("DF:FI").EntireColumn.Hidden = False
Range("EL:GG").EntireColumn.Hidden = True
Case "3/1/2014"
Range("F:BI").EntireColumn.Hidden = True
Range("AT:CW").EntireColumn.Hidden = True
Range("BZ:EC").EntireColumn.Hidden = True
Range("DF:FI").EntireColumn.Hidden = True
Range("EL:GG").EntireColumn.Hidden = False
End Select
Application.ScreenUpdating = True
End If
End Sub


Thanks!

 

[MikaChu]

I have a new macro but it only applies on the first condition. Any suggestions on how I can apply it with the other conditions?

Private Sub HideColumnsBydate()

Dim dStart As Long
Dim c As Range

dStart = Sheet1.Range("B9").value 'starting date to hide

Application.ScreenUpdating = False

With Sheet1
For Each c In Range("F12:GG12") ' row with date headers
If c.value < dStart Then
Range("F:BI").EntireColumn.Hidden = False
Range("AT:CW").EntireColumn.Hidden = True
Range("BZ:EC").EntireColumn.Hidden = True
Range("DF:FI").EntireColumn.Hidden = True
Range("EL:GG").EntireColumn.Hidden = True
End If

Next c

End With

Application.ScreenUpdating = True

End Sub