# Auto fill weekend day in grid

#### redspanna

##### Well-known Member
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

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### barry houdini

##### MrExcel MVP
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

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

##### Well-known Member
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

##### MrExcel MVP
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

##### Well-known Member
Barry,
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

##### MrExcel MVP
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

##### Well-known Member
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

Replies
2
Views
457
Replies
6
Views
238
Replies
0
Views
436
Replies
0
Views
236
Replies
2
Views
447

1,181,657
Messages
5,931,254
Members
436,785
Latest member
KingGideon

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back