Conditional formatting on a cell containing a formula

[Teladianium]

Hi all,

I am trying to build a calendar so I can change the year and get a new year. I have got the days of the month to work but I want to conditionally format the weekend days. I work where there are 2 weekends. Some operated Fri/Sat and the others Sat/Sun. so I need it to be dynamic. The snip below is showing my issue. I need the weekend to colour for the days of the month that are numbered and not for the days that are blank (Feb 29)

I am not too great with excel and relying on copy/pasting with modification to get stuff to work. so please bare with me.

The formula in the row for the dates is =IF(MONTH($A14)<>MONTH($A14-WEEKDAY($A14,1)+(COLUMN(AE14)-COLUMN($B14)+1)),"",$A14-WEEKDAY($A14,1)+(COLUMN(AE14)-COLUMN($B14)+1))
this is giving the desired effect allowing me to change the year and the months then reflect accordingly. so all the cells that need conditional formatting have formulas in them.

I have tried things like
ISFORMULA(AJ22>0)
ISFORMULA(AJ22=10) "Just to see if it returned something"

but to be honest I am just stabbing in the dark. Any help is really appreciated.

 

[hajiali]

what distinguishes if Fri/Sat or Sat/Sun are the weekend? Also would be best if you use XL2BB to copy your data in the post

 

[Teladianium]

Hi Hajiali,

Thanks for your reply,
I have a dropdown data validation for the weekend selection in cell R3. I have conditional formatting to manage the colours on the days row.

I had to google XL2BB . so trying to use it now. hope it works.

Book8
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
1	Rotation Tracker												Year:					2022
2													Schedual start:					Today					Q	Date of Flight					c	Trg / Course
3													Week end:					Fri - Sat					e Expected days						o	Other						L	Loan
4
5
6
7		Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo
8	2022 Calendar
9	2022	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo	Tu	We	Th	Fr	Sa	Su	Mo
10	Jan							1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	Jan
11
12
13
14	Feb			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28								Feb
15
16
17
18	Mar			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31					Mar
19
20
21
Calendar

Cell Formulas
Range	Formula
B10:AL10,B18:AL18,B14:AL14	B10	=IF(MONTH($A10)<>MONTH($A10-WEEKDAY($A10,1)+(COLUMN(B10)-COLUMN($B10)+1)),"",$A10-WEEKDAY($A10,1)+(COLUMN(B10)-COLUMN($B10)+1))
A10	A10	=DATE($R$1,1,1)
A14	A14	=DATE($R$1,2,1)
A18	A18	=DATE($R$1,3,1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H10,H14,H18,H22,H26,H30,O10,O14,O18,O22,O26,O30,V10,V14,V18,V22,V26,V30,AC10,AC14,AC18,AC22,AC26,AC30,AJ10,AJ22,AJ18,AJ14,AJ26,AJ30,H35,H39,H43,H47,H51,H55,O35,O39,O43,O47,O51,O55,V35,V39,V43,V47,V51,V55,AC35,AC39,AC43,AC47,AC51,AC55,AJ35,AJ39,AJ43,AJ47	Expression	="Isformula($AJ22=30)"	text	NO
G9,N9,U9,AB9,AI9	Expression	=$R$3="Fri - Sat"	text	NO
B9,I9,P9,W9,AD9,AK9	Expression	=$R$3="Sat - Sun"	text	NO
B9,I9,P9,W9,AD9,AK9	Expression	=$R$3="Fri - Sat"	text	NO
G7,N7,U7,AB7,AI7	Expression	=$R$3="Fri - Sat"	text	NO
B7,I7,P7,W7,AD7,AK7	Expression	=$R$3="Sat - Sun"	text	NO
B7,I7,P7,W7,AD7,AK7	Expression	=$R$3="Fri - Sat"	text	NO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63	Cell Value	="N"	text	NO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63	Cell Value	="L"	text	NO
AC20:AH20	Cell Value	="C"	text	NO
AC20:AH20	Cell Value	="x"	text	NO
AD16:AE16	Cell Value	="C"	text	NO
AD16:AE16	Cell Value	="x"	text	NO
D20	Cell Value	="C"	text	NO
D20	Cell Value	="x"	text	NO
E20	Cell Value	="C"	text	NO
E20	Cell Value	="x"	text	NO
W20	Cell Value	="C"	text	NO
W20	Cell Value	="x"	text	NO
N20:V20	Cell Value	="C"	text	NO
N20:V20	Cell Value	="x"	text	NO
M20	Cell Value	="C"	text	NO
M20	Cell Value	="x"	text	NO
Y16:AC16	Cell Value	="C"	text	NO
Y16:AC16	Cell Value	="x"	text	NO
X16	Cell Value	="C"	text	NO
X16	Cell Value	="x"	text	NO
AJ12	Cell Value	="C"	text	NO
AJ12	Cell Value	="x"	text	NO
W12	Cell Value	="C"	text	NO
W12	Cell Value	="x"	text	NO
J12:V12	Cell Value	="C"	text	NO
J12:V12	Cell Value	="x"	text	NO
H12:I12	Cell Value	="C"	text	NO
H12:I12	Cell Value	="x"	text	NO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63	Cell Value	="e"	text	NO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63	Cell Value	="u"	text	NO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63	Cell Value	="o"	text	NO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63	Cell Value	="C"	text	NO
H11:AL11,D15:AE15,F44:AI44,H48:AL48,E71:AI71,C79:AG79,F83:AI83,H88:AL88,D92:AH92,G96:AJ96,B100:AF100,E104:AH104,G108:AK108,D19:AH19,G23:AJ23,B27:AF27,E31:AH31,G36:AK36,C40:AG40,D52:AG52,E67:AF67,H75:AK75,F56:AJ56,B63:AF63	Cell Value	="x"	text	NO

Cells with Data Validation
Cell	Allow	Criteria
R2:T2	List	Start of year, Today, select date
R3:T3	List	Fri - Sat, Sat - Sun

 

[hajiali]

Try This Formula in The Conditional Formatting for the days Keep in mind that the value in R3 has to match "FRI - SAT" or "SAT - SUN" with the space in between unless you change the formula below

=OR(AND($R$3="SAT - SUN",OR(TEXT(B10,"DDD")="SAT",TEXT(B10,"DDD")="SUN")),AND($R$3="FRI - SAT",OR(TEXT(B10,"DDD")="FRI",TEXT(B10,"DDD")="SAT")))

 

[Teladianium]

Absolutely brilliant. Thank you Hajiali.
Works exactly as I need. Much appreciated

 

[hajiali]

No problem glad I could Help