Best Formula or VBA to Highlight range of dates on a Calendar?

[pvtjoker77]

Hello Friends,

I'm trying figure out the best way to highlight the users Days Off on a calendar based off of their selection of dates.

So on Sheet1 I have a Calendar and on that same sheet I have a Form control spinner for the user to select a Letter B thru J

and on sheet2 starting at Row 2 I have the 11 columns of 130+ rows of dates for that year that represents all of their scheduled days off.

I'm trying to make it that if the user selects B or whatever letter on the spinner all the dates on that particular column highlight on the calendar like I have some holidays highlighted.

I'm not quite sure how I can make this happen weather is through VBA or formulas.. Any help is appreciated!

Thank you in advance

 

[richh]

Hi pvtjoker,

Here's one way to do it:

1. Ensure that the days in your calendar are actual dates, not just a number that represents the day.
   1. Then, format the calendar's cells to be formatted with a custom format of "dd" to just see the day values.
2. Highlight the range of cells that cover the calendar.
3. Apply a Conditional Format using a Custom New Rule.
   1. Use this formula and modify to meet you sheets.
      1. =COUNTIF(INDIRECT("Sheet2!"&$A$4&"2:"&$A$4&"6"),C2)
         1. $A$4 = the cell that the the letter "B" appear in your example
         2. "6" = the last row that your staff have days off on.
            1. Could be set to like... 100 or something. That's up to you to determine
         3. C2 = the first cell in the calendar's date range.

 

[pvtjoker77]

Hi pvtjoker,

Here's one way to do it:

1. Ensure that the days in your calendar are actual dates, not just a number that represents the day.
   1. Then, format the calendar's cells to be formatted with a custom format of "dd" to just see the day values.
2. Highlight the range of cells that cover the calendar.
3. Apply a Conditional Format using a Custom New Rule.
   1. Use this formula and modify to meet you sheets.
      1. =COUNTIF(INDIRECT("Sheet2!"&$A$4&"2:"&$A$4&"6"),C2)
         1. $A$4 = the cell that the the letter "B" appear in your example
         2. "6" = the last row that your staff have days off on.
            1. Could be set to like... 100 or something. That's up to you to determine
         3. C2 = the first cell in the calendar's date range.

View attachment 43778

Is it possible to make it work if the cells were Custom formatted to "d" vs "dd"? just wondering.. Other than that it worked like a charm! Thank you again!!!!

 

[richh]

Yes, just a single "d" is fine. Formatting just masks the cell's contents, it doesn't alter their value.

 

[pvtjoker77]

When I

Yes, just a single "d" is fine. Formatting just masks the cell's contents, it doesn't alter their value.

Worked fine Thank you so much!