Afternoon!
Hoping someone can stop me banging my head on my desk.
I have a spreadsheet that is acting as a project/process tracker where users are required to provide a weekly update. There are 52 cells populated with the week commencing date for these updates to be provided. To make the spreadsheet easier to read I have decided to only show 3 cells at a time, last week, this week and next week. This is populated in the cells below the dates and is calculated using a nested IF statement based on a target cell to be popluated by the user (i.e. if the date entered by the user equals one of the dates in the 52 columns then it is populated with 'This week' and so on). All cells outside of this range are populated with 'Hide'
I have a simple piece of VBA that does the job but relies on manually running the macro and would like to change the hidden columns when the target cell changes. I've tried additing worksheet_change (byVal target as rnage) and then target.address = but this never seems to work. Also tried application.enableevent = false/true etc... but again no joy
VBA is as follows
Sub DisplayDates()
Application.ScreenUpdating = False
Dim cell As Range
For Each cell In Range("i2:br2")
If cell.Value = "Hide" Then
cell.EntireColumn.Hidden = True
End If
Next
End Sub
Anyone that can automate this based on a change in the target cell (which is currently D1) would be a life saver, well not literally but I would greatly appreciate the help.
Bookmarks