Auto fill weekend day in grid

[redspanna]

Hi all,


In Cell B3 of my worksheet is the word Jan, C3:AG31 are the numbers 1:31 - this represents January - days 1-30

Is there some code or other 'quick' way of shading the cell of the days(C3:AG31) that are weekends, and also shade the correspond cells immediately below,

for example if C3 holds day1 of Jan and this is a Friday then this cell and C4 would be left unshaded, however D3 (day2) is a Saturday so this cell and D4 would be shaded as it is a weekend day

hope I have explained this and thanks in advance

 

[barry houdini]

You can do it this way

Select C3:AG4 with C3 active cell

Use conditional formatting "formula is" option with the formula

=WEEKDAY(C$3&$B$3,2)>5

select required shading

Note: that this will shade cells according to the current year so as soon as we reach 1st Jan 2008 the shading will change. If you want to fix the year then, assuming you have the year, e.g. 2007 in B4 use the formula

=WEEKDAY(C$3&$B$3&$B$4,2)>5

 

[redspanna]

Barry,
Thanks for your suggestion, which works just great.
However can I add something else

B5 Holds the word FEB and cells C5:AD5 hold days 1:28
B7 Holds the word MAR and cells C7:AG7 hold days 1:31

etc etc, can your formula be adapted so that the whole worksheet can be correctly filled with shaded cells for each set of weekend days throughout the whole year - if so how?

Many thanks

 

[barry houdini]

Assuming the cells B4,B6,B8 etc. are blank you could try selecting the range

C3:AG26

and applying this formula

=WEEKDAY(IF($B3="",C2&$B2,C3&$B3),2)>5

 

[redspanna]

Barry,
Thanks again for your help,
just one slight error in the formula (or it could be me), can you check it for me

I highlight cells C3:AG26 and copy the formula as given, however everthing is correct except for the JAN rows.
The days are shaded correctly but nothing is shaded in the row below (C4:AG4)

any ideas?
thanks again

 

[barry houdini]

Is B4 blank? If B4 contains anything (even a space) then the previous formula I posted won't work.

If you can't guarantee that B4, B6, B8 etc. will be blank try using

=WEEKDAY(OFFSET(C3,-MOD(ROW()-ROW(C$3),2),)&OFFSET($B3,-MOD(ROW()-ROW($B$3),2),),2)>5

 

[redspanna]

Sorry my error, as B4 contained the year ie 2007 - easy to take that out and now it works just great - thanks ever so much

Cheers