Conditional Format Holidays
 

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

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


--
Regards
Frank Kabel
Frankfurt, Germany

GregR wrote:
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, thank you very much

Greg
"Frank Kabel" wrote in message
...
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))


--
Regards
Frank Kabel
Frankfurt, Germany

GregR wrote:
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