When should the column(s) with a heading earlier than the current system date be hidden?
Should this be upon opening the workbook, when a heading is changed (edited) or added, or when a manual action is taken (for example, clicking a button)?
If the workbook is open on the cusp of one day to the next (e.g. opened at 11:55pm, & being worked upon beyond midnight), should the column representing 'yesterday' be automatically hidden at 12midnight?
Something like
Private Sub Worksheet_Change(ByVal Target As Range)
If cells C1:AF 1 older than todays date then
Columns("C:AF").EntireColumn.Hidden = True
Else
Columns("C:AF").EntireColumn.Hidden = False
End If
End Sub
any ideas?
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim objCell As Range
Application.ScreenUpdating = False
' For Each objCell In [C:AF].EntireColumn ' Alternatively,...
For Each objCell In Range([C1], Cells(1&, [C:AF].SpecialCells(xlCellTypeLastCell).Column)).EntireColumn
If IsDate(objCell.Cells(1&)) Then
objCell.Hidden = (objCell.Cells(1&) < Date)
End If ' If IsDate(objCell.Cells(1&)) Then
Next objCell
Application.ScreenUpdating = False
End Sub