Autofill Color for all weekends

[HendrikWiese]

Hi Mr. Excel,

Hope you can help.

I have a spreadsheet (Excel 2010) that autofill the days of the month based on the following

=DATE(YEAR($A$2),MONTH($A$2),1) where A2 has got a format of mmm yyyy

I use the +1 etc. at the end for the days that follow. Now the formula above is in 2 columns one with a format of ddd and the one below it with a format of d so I have the following output if I have entered Mar 2011 in A2.

Wed
1

Now I would like to know how I would go about to Autofill all weekends (In other words Sat, Sun and Public holidays(If Possible)) with a light green color upon changing A2

Looking forward to your reply

 

[GlennUK]

Assuming that you have the Public Holidays stored somewhere ( maybe a named range called Holidays ), then use Conditional Formatting, with a condition of ( formula is ):

=OR(WEEKDAY(mycellref,2)>5,NOT(ISNA(MATCH(mycellref,Holidays,0))))

... replacing mycellref with the active cell reference.

 

[HendrikWiese]

Thank you for the reply. Would the above formula change the cell color? If it would be easier, lets leave the public holidays for now and just get it to change the cells color for every Sat and Sun.

Looking forward to your response.
Regards,
HendrikWiese

 

[GlennUK]

As I mentioned in my post, you use that formula in Conditional Formatting.

 

[HendrikWiese]

Sorry don't know how I missed that in your first post. I tried conditional formatting but not sure how to set it up. Can you maybe send me steps to accomplish this?

 

[GlennUK]

Select A3:B99 and do Conditional Formatting, New Rule, Use a formula to determine which cells to format, and do a formula of :

=OR(WEEKDAY($A3,2)>5,NOT(ISNA(MATCH($A3,Holidays,0))))

if you have Holidays set up, or this:

=WEEKDAY($A3,2)>5

if you don't. Choose the format you want when the condition is met. ( probably Fill in your case, and choose whatever colour )