Conditional Format Holidays

G

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
 
F

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))
 
G

GregR

Frank, thank you very much

Greg
Frank Kabel said:
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))
Click to expand...
 
Top