Hide Columns Based on Date Range And Todays Date

Nonebutshininghours

New Member
Joined
May 25, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. MacOS
  2. Web
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!
1621955256991.png
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

JamesCanale

Board Regular
Joined
Jan 13, 2021
Messages
238
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I don't think you need VBA. Have a master schedule sheet like this, with all the dates:
MrExcelPlayground2.xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
2Name5/16/20215/17/20215/18/20215/19/20215/20/20215/21/20215/22/20215/23/20215/24/20215/25/20215/26/20215/27/20215/28/20215/29/20215/30/20215/31/20216/1/20216/2/20216/3/20216/4/20216/5/20216/6/20216/7/2021
3JamieXXXZZXXXXXZZXXXXXZZZxxx
4Harryzzzxxzzzzzxxzzzzzxxxzzz
5Fredxxxxxxxxxxxxxx
6Sallyxxxxxxxxxxx
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:
Cell Formulas
RangeFormula
B2:N2B2=TODAY()+B1
A3:A6A3=SchedData!A3
B3:N6B3=INDEX(SchedData!$B3:$AN3,MATCH(B$2,SchedData!$B$2:$AN$2,0))
 

Nonebutshininghours

New Member
Joined
May 25, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. MacOS
  2. Web
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

Board Regular
Joined
Jan 13, 2021
Messages
238
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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:
Cell Formulas
RangeFormula
B2:N2B2=TODAY()+B1
A3:A6A3=SchedData!A3
B3:N6B3=INDEX(SchedData!$B3:$AN3,MATCH(B$2,SchedData!$B$2:$AN$2,0))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B3:N6Expression=B3=0textNO
B3:N6Expression=B3="z"textNO
B3:N6Expression=B3="X"textNO
 

Forum statistics

Threads
1,137,154
Messages
5,679,911
Members
419,862
Latest member
Bluewings666

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top