Need help to set color for weekend Column

[jymoua]

On cell A1 = Month, I enter the month in cell A1. The month here will change monthly
On A2:AF2 are reference from A1 but format as weekday and will change according to the month on A1. On A3:AF3 also reference from A1 but format as date and will change according to the month on A1 also. I need macro to color the weekend column from row 2 through row 15 every time I enter a new month in A1. Is this possible? Appreciate for anyone who can help me.

 

[Andrew Fergus]

Hi

If I am understanding your question correctly then I believe you can do this using conditional formatting, and you don't need a macro. Assuming the data in row 2 contains a date value (but is formatted as a weekday) then try this:

Select the range A2:A15, select menu option Format > Conditional Formatting > change 'Cell Value is' to 'Formula Is' > in the right hand box enter the following formula:

=OR(WEEKDAY(A$2)=1,WEEKDAY(A$2)=7)

then click 'Format' > select the format you want > Ok > Ok. Then copy the range A2:A15 and paste special the formats (menu option Edit > Paste Special > Formats > Ok) onto cells B2:AF15. This should set the format for rows 2:15 where the day is Saturday or Sunday to whatever format you selected under the conditional formatting settings.

I trust that helps!
Andrew

 

[jymoua]

It works perfectly. Thanks a million.

 

[Peter_SSs]

jymoua

You could simplify that Conditional Formatting formula.

Instead of
=OR(WEEKDAY(A$2)=1,WEEKDAY(A$2)=7)

try
=WEEKDAY(A$2,2)>5