Conditional Formatting on pivot-based calendar for vacation planner

kelle

Board Regular
Joined
Apr 1, 2015
Messages
93
I'm working on rebuilding a yearly vacation planner (based on May-April, our vacation planning year) that our organization uses and am looking for help on conditional formatting. I've been asked to keep the previous formatting, which is why I have the pivot-based calendar appearing in the top part of the document, but also to add functionality for staff to mark off individual vacation days with the appropriate amount of hours in row 12 downwards (each row will represent one employee).

What I would LIKE to do is, when Emp1 marks that they are taking 7 hours of vacation on May 1 2018 in row 12, conditional formatting is applied on the matching date in the calendar at the top--in other words, if they mark 7 hours on May 1 2018 in cell H12, merged cell AA4:AD4 representing May 1 2018 on the calendar bolds, or something like that. I am absolutely unable to figure out the formula for this, though, especially considering I really don't feel like inserting 365/6 individual conditional formatting rules for every day of the year... is there some way to do this with a specific range (say, a whole month, so H4:AL9 for May) or maybe one row at a time for the whole year?

vacationcalendar.jpg
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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
Back
Top