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?We want to look at the cells in the odd-numbered columns E, G, I etc.
The conditional formatting formula refers to the top left cell of the selected range: E5, but it will dynamically adjust it for the other cells.
COLUMN(E5) returns the column number (5 in this case, but 6 for F5, 7 for G5 etc.).
ISEVEN(COLUMN(E5)) returns TRUE if the column number is even, and FALSE if it is odd.
In calculations, Excel treats TRUE as 1 and FALSE as 0, so -ISEVEN(COLUMN(E5)) is -1 for even-numbered columns and 0 for odd-numbered columns.
OFFSET(E5,0,-ISEVEN(COLUMN(E5))) is equivalent to OFFSET(E5,0,0), so to E5.
OFFSET(F5,0,-ISEVEN(COLUMN(F5))) is equivalent to OFFSET(F5,0,-1), so also to E5.
OFFSET(G5,0,-ISEVEN(COLUMN(G5))) is equivalent to OFFSET(G5,0,0), so to G5.
OFFSET(H5,0,-ISEVEN(COLUMN(H5))) is equivalent to OFFSET(H5,0,-1), so also to G5.
Etc. - this OFFSET always returns the cell in column E, G, I etc.
OFFSET(E5,0,-ISEVEN(COLUMN(E5)))="Sat" is TRUE = 1 for Saturday, and FALSE = 0 for other days of the week.
OFFSET(E5,0,-ISEVEN(COLUMN(E5)))="Sun" is TRUE = 1 for Sunday, and FALSE = 0 for other days of the week.
Added together, you get 1 for Saturday and Sunday, and 0 for other days of the week.
I'd need to know more about the way you set up the vacation data.