Forum Discussion
Excel Yearly calender conditional formatting
Select the cells that you want to format conditionally.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Lave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
Enter ="Sat" in the box next to it.
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
Repeat the above steps, but with ="Sun" instead of ="Sat".
Select the cells that you want to format conditionally.
On the Home tab of the ribbon, select Conditional Formatting > New Rule...
Select 'Format only cells that contain'.
Lave the first drop down set to 'Cell Value'.
Select 'equal to' from the second drop down.
Enter ="Sat" in the box next to it.
Click Format...
Activate the Fill tab.
Select green.
Click OK, then click OK again.
Repeat the above steps, but with ="Sun" instead of ="Sat".
Hey! I thought i tried this but i think i used formula instead. Somehow i messed up. This works thank you. However i also want the color next to that cell coloured do you know how i could do that? Color 1 cell to the right of the valued one. See picture for example. I want it to automatticly do what i did manually. It is just red so u can see it easily but it will be the same color as Sat/Sun
Select the entire range you want to format conditionally. It's probably E5:AB35.
E5 should be the active cell in the selection.
Delete any existing conditional formatting rules.
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
=(OFFSET(E5,0,-ISEVEN(COLUMN(E5)))="Sat")+(OFFSET(E5,0,-ISEVEN(COLUMN(E5)))="Sun")
Click Format... and proceed as usual.
HansVogelaar Thanks works like a charm!
I do need to do more things but i can use your formula and remake it so it fits, Any chance of u explaining how this formula works?
=(OFFSET(E5,0,-ISEVEN(COLUMN(E5)))="Sat")+(OFFSET(E5,0,-ISEVEN(COLUMN(E5)))="Sun")
I am very confused on how that works but im happy it does. I also need to enter vacations into the calender which i've got a sepperate list for just like how i determine the months/days/year. I got 2 tables. 1 start of vacation date and an end of vacation date. Would i be able to use the OFFSET formula for this?
