Hide Columns based on Date value in cell (Worksheet Change event)

  • [h=2]Hide Columns based on Date value in cell (Worksheet Change event)[/h][INDENT]Looking for a worksheet_change event macro that will automatically hide columns based on a value in cell S3. S3 is a data validation pick list of months based on the 1st day of each (e.g. 1/1/2014, 2/1/2014, 3/1/2014, etc.). I have date columns in row 6 from columns T through AQ. These values are 1/1/2014 (T6), 2/1/2104 (U6), 3/1/2014 (V6)....12/1/2015 (AQ6). I would like to hide columns that have a date in row 6 (T6:AQ6) that is LESS THAN (<) the date in S3 after the cell is value is changed.


    Hope this makes sense. Let me know if you have any ?'s.


    Thank you!!![/INDENT]

  • Re: Hide Columns based on Date value in cell (Worksheet Change event)


    Hi GoCavs :)


    Give the following a try.


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. Dim rng As Range
    3. Application.ScreenUpdating = False
    4. If Not Intersect(Target, Range("S3")) Is Nothing Then
    5. For Each rng In Range("T6:AQ6")
    6. rng.EntireColumn.Hidden = (rng < [S3])
    7. Next rng
    8. End If
    9. Application.ScreenUpdating = True
    10. End Sub


    Will attach a workbook to show workings. Can also learn more about this here Hide Columns


    Take care


    Smallman