Auto fill weekend day in grid

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,575
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 23, 2005
Messages
20,825
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

Well-known Member
Joined
Jul 27, 2005
Messages
1,575
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jul 27, 2005
Messages
1,575
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jul 27, 2005
Messages
1,575
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top