Hide columns if header date falls in range

[sparkytech]

I am creating a 6 week lookahead containing "week beginning" dates for an entire year. There is a drop down where a user can select "Auto Date" and it will start with the current week, or they can select "Manual Date" and input a date. This start date is in cell A6, and in row B18 (from B18 to BA18) I have the "week beginning" dates. Whenever the date in A6 is changed, I would like to unhide everything, and then hide any columns with header dates before the start date, and also anything greater than 6 weeks past that. Basically, I want the sheet to display 6 "weeks" of columns based on the user inputted date. Seems like a simple task, and there is probably a cleaner way to do this. I'm new to VBA, and I can't seem to figure this one out... any help is appreciated!

Sub Hide_Columns_Containing_Value()
lngStart = Range("Sheet1!A6").Value
lngEnd = Range("Sheet1!A7").Value
    
Dim c As Range

    For Each c In Range("B18:BA18").Cells
        If c.Value <= lngStart Or c.Value > lngEnd Then
            c.EntireColumn.Hidden = True

        End If
    Next c

End Sub

 

[maabadi]

Try this:

Sub Hide_Columns_Containing_Value()
Dim lngStart As Long, lngEnd As Long
lngStart = Sheets("sheet1").Range("A6").Value
lngEnd = Sheets("sheet1").Range("A7").Value
    
Dim c As Range

    For Each c In Range("B18:BA18")
        If c.Value <= lngStart Or c.Value > lngStart + 40 Then
            c.EntireColumn.Hidden = True

        End If
    Next c

End Sub

 

[sparkytech]

Thanks! It's sort of working... it hides the header columns when I change dates, but the "tasks" I have in the date columns never "shift" to stay with the correct date. Any ideas?

 

[maabadi]

Please upload Example file & desired Result with XL2BB ADDIN (Preferable) or upload your file at free hosting site e.g. www.dropbox.com, GoogleDrive or Onedrive & input link here.
Why don't use Today() or Now() function at A6

 

[sparkytech]

Please upload Example file & desired Result with XL2BB ADDIN (Preferable) or upload your file at free hosting site e.g. www.dropbox.com, GoogleDrive or Onedrive & input link here.
Why don't use Today() or Now() function at A6

Here you go:

2021 Project Lookahead ~ rev 5.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2
3	Auto Date
4	Manual Date
5
6	1/9/2021
7	2/20/2021
8
9
10
11
12
13	Today is: 01/09/2021
14
15
16
17	Auto Date	January-2021	January-2021	January-2021	January-2021	January-2021	February-2021	February-2021	February-2021	February-2021
18	Input Manual Date: 01/18/2021	1/3/2021	1/10/2021	1/17/2021	1/24/2021	1/31/2021	2/7/2021	2/14/2021	2/21/2021	2/28/2021
19	Description
20	Task 1	1	8	15
21	Task 2	2	9	16
22	Task 3	3	10	17
23		4	11	18
24		5	12	19
25		6	13	20
26		7	14	21
27
28
29
30
31
32
33
34
35
Sheet1

Cell Formulas
Range	Formula
A6	A6	=IF(A17="Auto Date",TODAY(),A18)
A7	A7	=A6+42
A13	A13	=TODAY()
B17:J17	B17	=TEXT(B18,"mmmm-yyyy")
B18	B18	=IF(A17="Manual Date",((((A18+1)+G3)-0)-WEEKDAY((((A18+1)+G3)-0),2)),((TODAY())-WEEKDAY((TODAY()),2)))
C18:J18	C18	=B18+7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D17	Expression	=D$17<>C$17	text	NO
D17	Expression	=ISEVEN(MONTH(D17))	text	NO
C17	Expression	=C$17<>B$17	text	NO
C17	Expression	=ISEVEN(MONTH(C17))	text	NO
J34	Dates Occurring	this week	text	NO
J34	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )	text	NO
I34	Dates Occurring	this week	text	NO
I34	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
H34	Dates Occurring	this week	text	NO
H34	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
B34	Dates Occurring	this week	text	NO
B34	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B34	Expression	=(MONTH(B34)<>MONTH(C34))	text	NO
C34:G34	Dates Occurring	this week	text	NO
C34:G34	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )	text	NO
C34:F34	Expression	=(MONTH(C34)<>MONTH(D34))	text	NO
C34:F34	Expression	=(MONTH(C34)<>MONTH(D34))	text	NO
J32	Dates Occurring	this week	text	NO
J32	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )	text	NO
I32	Dates Occurring	this week	text	NO
I32	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
H32	Dates Occurring	this week	text	NO
H32	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
B32	Dates Occurring	this week	text	NO
B32	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B32	Expression	=(MONTH(B32)<>MONTH(C32))	text	NO
C32:G32	Dates Occurring	this week	text	NO
C32:G32	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )	text	NO
C32:F32	Expression	=(MONTH(C32)<>MONTH(D32))	text	NO
C32:F32	Expression	=(MONTH(C32)<>MONTH(D32))	text	NO
J30	Dates Occurring	this week	text	NO
J30	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )	text	NO
I30	Dates Occurring	this week	text	NO
I30	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
H30	Dates Occurring	this week	text	NO
H30	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
B30	Dates Occurring	this week	text	NO
B30	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B30	Expression	=(MONTH(B30)<>MONTH(C30))	text	NO
C30:G30	Dates Occurring	this week	text	NO
C30:G30	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )	text	NO
C30:F30	Expression	=(MONTH(C30)<>MONTH(D30))	text	NO
C30:F30	Expression	=(MONTH(C30)<>MONTH(D30))	text	NO
J28	Dates Occurring	this week	text	NO
J28	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )	text	NO
I28	Dates Occurring	this week	text	NO
I28	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
H28	Dates Occurring	this week	text	NO
H28	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
B28	Dates Occurring	this week	text	NO
B28	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B28	Expression	=(MONTH(B28)<>MONTH(C28))	text	NO
C28:G28	Dates Occurring	this week	text	NO
C28:G28	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )	text	NO
C28:F28	Expression	=(MONTH(C28)<>MONTH(D28))	text	NO
C28:F28	Expression	=(MONTH(C28)<>MONTH(D28))	text	NO
J26	Dates Occurring	this week	text	NO
J26	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )	text	NO
I26	Dates Occurring	this week	text	NO
I26	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
H26	Dates Occurring	this week	text	NO
H26	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
B26	Dates Occurring	this week	text	NO
B26	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B26	Expression	=(MONTH(B26)<>MONTH(C26))	text	NO
C26:G26	Dates Occurring	this week	text	NO
C26:G26	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )	text	NO
C26:F26	Expression	=(MONTH(C26)<>MONTH(D26))	text	NO
C26:F26	Expression	=(MONTH(C26)<>MONTH(D26))	text	NO
J24	Dates Occurring	this week	text	NO
J24	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )	text	NO
I24	Dates Occurring	this week	text	NO
I24	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
H24	Dates Occurring	this week	text	NO
H24	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
B24	Dates Occurring	this week	text	NO
B24	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B24	Expression	=(MONTH(B24)<>MONTH(C24))	text	NO
C24:G24	Dates Occurring	this week	text	NO
C24:G24	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )	text	NO
C24:F24	Expression	=(MONTH(C24)<>MONTH(D24))	text	NO
C24:F24	Expression	=(MONTH(C24)<>MONTH(D24))	text	NO
J22	Dates Occurring	this week	text	NO
J22	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )	text	NO
I22	Dates Occurring	this week	text	NO
I22	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
H22	Dates Occurring	this week	text	NO
H22	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
B22	Dates Occurring	this week	text	NO
B22	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B22	Expression	=(MONTH(B22)<>MONTH(C22))	text	NO
C22:G22	Dates Occurring	this week	text	NO
C22:G22	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )	text	NO
C22:F22	Expression	=(MONTH(C22)<>MONTH(D22))	text	NO
C22:F22	Expression	=(MONTH(C22)<>MONTH(D22))	text	NO
I35:BA35	Dates Occurring	this week	text	NO
I35:BA35	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
H35	Dates Occurring	this week	text	NO
H35	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
J25	Cell	contains a blank value	text	NO
J25	Expression	=OR(J$25="None",J$25="none",J$25="N/A",J$25="n/a",J$25="NA",J$25="na")	text	NO
J27	Dates Occurring	this week	text	NO
J27	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )	text	NO
J17	Expression	=J$17<>I$17	text	NO
J17	Expression	=ISEVEN(MONTH(J17))	text	NO
J18:J21	Dates Occurring	this week	text	NO
J18:J21	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=J$18-WEEKDAY(J$18,17 )	text	NO
I25	Cell	contains a blank value	text	NO
I25	Expression	=OR(I$25="None",I$25="none",I$25="N/A",I$25="n/a",I$25="NA",I$25="na")	text	NO
I27	Dates Occurring	this week	text	NO
I27	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
I17	Expression	=I$17<>H$17	text	NO
I17	Expression	=ISEVEN(MONTH(I17))	text	NO
I18:I21	Dates Occurring	this week	text	NO
I18:I21	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=I$18-WEEKDAY(I$18,17 )	text	NO
H25	Cell	contains a blank value	text	NO
H25	Expression	=OR(H$25="None",H$25="none",H$25="N/A",H$25="n/a",H$25="NA",H$25="na")	text	NO
H27	Dates Occurring	this week	text	NO
H27	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
H17	Expression	=H$17<>G$17	text	NO
H17	Expression	=ISEVEN(MONTH(H17))	text	NO
H18:H21	Dates Occurring	this week	text	NO
H18:H21	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=H$18-WEEKDAY(H$18,17 )	text	NO
B25:G25	Cell	contains a blank value	text	NO
B25:G25	Expression	=OR(B$25="None",B$25="none",B$25="N/A",B$25="n/a",B$25="NA",B$25="na")	text	NO
A17	Cell Value	="Manual Date"	text	NO
B17	Expression	=B$17<>A$17	text	NO
B17	Expression	=ISEVEN(MONTH(B17))	text	NO
B27	Dates Occurring	this week	text	NO
B27	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B27,B29,B31,B33,B35	Expression	=(MONTH(B27)<>MONTH(C27))	text	NO
B27,B29,B31,B33,B35	Expression	=(MONTH(B27)<>MONTH(#REF!))	text	NO
B27,B29,B31,B33,B35	Expression	=(MONTH(B27)<>MONTH(C27))	text	NO
B27,B29,B31,B33,B35	Expression	=(MONTH(B27)<>MONTH(#REF!))	text	NO
C27:G27	Dates Occurring	this week	text	NO
C27:G27	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=C$18-WEEKDAY(C$18,17 )	text	NO
G17	Expression	=G$17<>F$17	text	NO
G17	Expression	=ISEVEN(MONTH(G17))	text	NO
F17	Expression	=F$17<>E$17	text	NO
F17	Expression	=ISEVEN(MONTH(F17))	text	NO
E17	Expression	=E$17<>D$17	text	NO
E17	Expression	=ISEVEN(MONTH(E17))	text	NO
B19:B21	Dates Occurring	this week	text	NO
B19:B21	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B19:B21,B23,B25	Expression	=(MONTH(B19)<>MONTH(C19))	text	NO
B18:G18,C19:G21,B35:G35,B23:BA23,B25:BA25,B29:BA29,B31:BA31,B33:BA33	Dates Occurring	this week	text	NO
B18:G18,C19:G21,B35:G35,B23:BA23,B25:BA25,B29:BA29,B31:BA31,B33:BA33	Expression	=TODAY()-WEEKDAY(TODAY(),17 )=B$18-WEEKDAY(B$18,17 )	text	NO
B18:F18,C19:F21,C23:F23,C25:F25	Expression	=(MONTH(B18)<>MONTH(C18))	text	NO
B18:F18,C19:F21,C23:F23,C25:F25	Expression	=(MONTH(B18)<>MONTH(C18))	text	NO
A18	Expression	=$A$17="Auto Date"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
A19:J19	Any value
B18	Any value
A20:A35	Any value
A17	List	=$A$3:$A$4
A18	Date	between 1/1/1900 and 12/31/3000

 

[maabadi]

I have in the date columns never "shift" to stay with the correct date

What means exactly. Please give examples

 

[sparkytech]

I will have multiple "task" rows for each week (column) on this 6 week lookahead. Say for example, I enter tasks for 6 months. If I display the first 6 weeks of the year, those tasks will be shown under their dates. If I manually enter a date in April, it should move the date range and show the tasks that are in those columns.

 

[maabadi]

then I think you want worksheet Change event to Whenever you change Date at Cell A6 (I think), it runs and hide unwanted columns.
Are I Correct?

 

[maabadi]

1. if you want fixed date at row 18. you should add fixed date at Cell B18 Not Formula to when Change Cell value at A6 or A18 Dates don't Change at Row 18.

2. if you want worksheet_ChangeEvent , go to sheet Name (sheet tab name at bottom at excel Page), Rigt Click, Select View Code
At the right window Delete all code if exists then Paste this.
with this code when you change cell A17 to Auto or Manual Date ( i add Data validationtoit Also) Code Working.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lngStart As Long, lngEnd As Long
Application.EnableEvents = False
If Not Application.Intersect(Range("A17"), Target) Is Nothing Then

lngStart = Sheets("sheet1").Range("A6").Value
'lngEnd = Sheets("sheet1").Range("A7").Value
 Range("B1:BA1").EntireColumn.Hidden = False
Dim c As Range

    For Each c In Range("B18:BA18")
        If c.Value + 1 <= lngStart Or c.Value > lngStart + 40 Then
            c.EntireColumn.Hidden = True

        End If
    Next c
End If
Application.EnableEvents = True
End Sub

 

[sparkytech]

You are correct regarding the worksheet changing dates. The sheet will have columns for 52 weeks, only 6 weeks at a time need to be shown.
The user has (2) options:
1. Select Auto Date - cell B18 will display the current week start date, columns to right of B18 will show the following 5 week beginning dates through G18.
2. Select Manual Date and enter a date manually - cell B18 will display the week start date for the manually entered date, columns to right of B18 will show the following 5 week beginning dates through G18.

It hides the date headers and shows 6 weeks, but the corresponding "task" is not hidden / unhidden. As you can see in the attachments, the numbers in the task columns never move. These columns should be hidden / unhidden based on the date as well. I hope that makes sense.