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
conditional formatting based on dates
-
- Administrator
- Posts: 78235
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: conditional formatting based on dates
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
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
Hans
-
- StarLounger
- Posts: 74
- Joined: 19 Apr 2010, 22:50
Re: conditional formatting based on dates
That worked perfect
Thanks heaps
Thanks heaps