Conditional Format Holidays

[GregR]

I have a worksheet with holidays listed in M2:M12. I want to conditionally
format a cell if the date is a holiday. Additionally, if the holiday falls
on Sat, I want the previous Fri. formatted and if the holiday falls on Sun,
the following Mon. TIA

Greg

 

[Frank Kabel]

Hi
lets assume your dates are in A1:A10 (and you have selected this range)
1. Format holidays. in the conditional format dialog enter the following
formula:
=COUNTIF($M$2:$M$12,A1)
and choose your format

2. Format the Fridays:
- select A1:A9
- open the conditional format dialog
- enter the following formula:
=AND(WEEKDAY(A1)=6,COUNTIF($M$2:$M$12,A2))

2. Format the Mondays:
- select A2:A10
- open the conditional format dialog
- enter the following formula:
=AND(WEEKDAY(A2)=2,COUNTIF($M$2:$M$12,A1))

 

[GregR]

Frank, thank you very much

Greg

Hi
lets assume your dates are in A1:A10 (and you have selected this range)
1. Format holidays. in the conditional format dialog enter the following
formula:
=COUNTIF($M$2:$M$12,A1)
and choose your format

2. Format the Fridays:
- select A1:A9
- open the conditional format dialog
- enter the following formula:
=AND(WEEKDAY(A1)=6,COUNTIF($M$2:$M$12,A2))

2. Format the Mondays:
- select A2:A10
- open the conditional format dialog
- enter the following formula:
=AND(WEEKDAY(A2)=2,COUNTIF($M$2:$M$12,A1))