Hide Columns Based on Date Range And Todays Date

[Nonebutshininghours]

My problem is a two parter!

I have an schedule that is laid out on one sheet. I plan on embedding the sheet to a website to have live on a screen for my employees to be able to see. The goal would be for them to see a range from 3 days before todays date through 21 days after todays date.

Here are my two questions:
1.)
The schedule is set up so that Row 4 (Starting with B:4) has sequential dates. We are constantly working on the schedule so embedding doesn't really work as if I'm working on the file I need to be able to be working on another months schedule without it changing what is viewed on the screen. So my question is can I make another sheet that contains the same data linked to my other sheet?

2.)
For The Sheet that I will embed, I want only want to show columns that are range from 3 days before todays date to 21 days after todays date. Can someone help me with the code for VBA?

I appreciate any help at all!

 

[JamesCanale]

I don't think you need VBA. Have a master schedule sheet like this, with all the dates:

MrExcelPlayground2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
2	Name	5/16/2021	5/17/2021	5/18/2021	5/19/2021	5/20/2021	5/21/2021	5/22/2021	5/23/2021	5/24/2021	5/25/2021	5/26/2021	5/27/2021	5/28/2021	5/29/2021	5/30/2021	5/31/2021	6/1/2021	6/2/2021	6/3/2021	6/4/2021	6/5/2021	6/6/2021	6/7/2021
3	Jamie	X	X	X	Z	Z	X	X	X	X	X	Z	Z	X	X	X	X	X	Z	Z	Z	x	x	x
4	Harry	z	z	z	x	x	z	z	z	z	z	x	x	z	z	z	z	z	x	x	x	z	z	z
5	Fred	x	x	x	x			x	x	x	x	x			x	x	x	x	x
6	Sally	x	x	x	x	x	x	x	x	x	x	x
SchedData

And then an output sheet like this that just shows today minus 3 to today plus whatever, and mix in some conditional formatting equations that give you the fill colors that you want based on the cell value:

MrExcelPlayground2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		-3	-2	-1	0	1	2	3	4	5	6	7	8	9
2	Name	5/22/2021	5/23/2021	5/24/2021	5/25/2021	5/26/2021	5/27/2021	5/28/2021	5/29/2021	5/30/2021	5/31/2021	6/1/2021	6/2/2021	6/3/2021
3	Jamie	X	X	X	X	Z	Z	X	X	X	X	X	Z	Z
4	Harry	z	z	z	z	x	x	z	z	z	z	z	x	x
5	Fred	x	x	x	x	x	0	0	x	x	x	x	x	0
6	Sally	x	x	x	x	x	0	0	0	0	0	0	0	0
Sheet26

Cell Formulas
Range	Formula
B2:N2	B2	=TODAY()+B1
A3:A6	A3	=SchedData!A3
B3:N6	B3	=INDEX(SchedData!$B3:$AN3,MATCH(B$2,SchedData!$B$2:$AN$2,0))

 

[Nonebutshininghours]

Thank you! They main identifiers they use on the schedule are the colors of the cells themselves. Is it possible that the colors transfer over to the new output sheet?

 

[JamesCanale]

I don't know how to make it 'read' the backcolor of the cell without vba.

You can use conditional formatting for colors on the output. Let them use characters (like x's) on the input sheet with all the dates, but then the output sheet can be something that if the cell value = 'x' then the backfill and the font color are both red; if it is 0, then they are both white, if it is other things, then a different color.

Like this:

MrExcelPlayground2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		-3	-2	-1	0	1	2	3	4	5	6	7	8	9
2	Name	5/22/2021	5/23/2021	5/24/2021	5/25/2021	5/26/2021	5/27/2021	5/28/2021	5/29/2021	5/30/2021	5/31/2021	6/1/2021	6/2/2021	6/3/2021
3	Jamie	X	X	X	X	Z	Z	X	X	X	X	X	Z	Z
4	Harry	z	z	z	z	x	x	z	z	z	z	z	x	x
5	Fred	x	x	x	x	x	0	0	x	x	x	x	x	0
6	Sally	x	x	x	x	x	0	0	0	0	0	0	0	0
Sheet26

Cell Formulas
Range	Formula
B2:N2	B2	=TODAY()+B1
A3:A6	A3	=SchedData!A3
B3:N6	B3	=INDEX(SchedData!$B3:$AN3,MATCH(B$2,SchedData!$B$2:$AN$2,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:N6	Expression	=B3=0	text	NO
B3:N6	Expression	=B3="z"	text	NO
B3:N6	Expression	=B3="X"	text	NO