conditional formatting based on dates

Jeanette
StarLounger
Posts: 74
Joined: 19 Apr 2010, 22:50

conditional formatting based on dates

Post by Jeanette »

Hi there
I have days in row 6 - Monday, Tuesday etc.. The whole year

in row 9 till about 20 I want to conditional format so the weekends are shaded.
So when I use the same worksheet next year and change the days, the shading will change automatically.

I use =WEEKDAY($6:$6,2)>5 when rows 9 till 20 are highlit, set the shading and bingo everything is shaded.

That should work, shouldn't it...
I am obviously overlooking something important
So which formula do I use
Thanks

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: conditional formatting based on dates

Post by HansV »

Let's say that the cells you want to highlight start in cell B9.
Select this range. I'll assume that B9 is the active cell within the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula =WEEKDAY(B$6,2)>5 where B is the column of the active cell.
Click Format...
Activate the Fill tab.
Select the desired shading, then click OK.
Click OK again.

If you selected entire rows, and A9 is the active cell within the selection, use =WEEKDAY(A$6,2)>5
Best wishes,
Hans

Jeanette
StarLounger
Posts: 74
Joined: 19 Apr 2010, 22:50

Re: conditional formatting based on dates

Post by Jeanette »

That worked perfect
Thanks heaps