Hi all,

I've been tasked with creating a calendar to help coordinate PTO usage for the employees in my work department. My company uses a schedule where we have every other Friday off. Each employee is on one of two schedules, A or B, which determines which Friday they work (when A-schedule employees are working any given Friday, the B-schedule employees are at home). I would like to set up conditional formatting so that each employee's off-Friday is highlighted. The following images shows how I would ultimately like each month to look:

temp.png

I currently have two conditional formatting rules in place to highlight weekends (Saturday and Sunday) and company paid holidays. I have a data tab set up as follows:

temp2.png

My current thought is to create two separate conditional formatting rules, one for each schedule, that test if the following statements are true:
- If John Johnson is working on the A schedule, then look up each Friday that he has off (based on the list of A-schedule Off Friday dates) for each month and highlight the matching cells.
- If Bob Bobson is working on the B schedule, then look up each Friday that he has off (based on the list of B-schedule Off Friday dates) for each month and highlight the matching cells.

I'm probably overthinking things and there's a much simpler method. Anyone have any thoughts on how I might accomplish this? Thanks in advance!