From Conditional Formatting to VBA

[Agnarr]

Hello everybody and you all do an amazing work. I need your help please and i apologize in advance for any mistakes (English is not my native language).
This one is kinda tricky and complicated.

I have created a calendar of shorts, where I input the work schedule and put a bunch of conditional formatting for color code and other things. The Thing is, I find this to be overly complicated and would like to transfer those conditional formatting rules into vba code, but I have no idea where or how to start.... I'm also open to any recommendations of making this more... tidy...
The formatting goes as follows:

=IF(B2="VACAY";TRUE;FALSE) 
=IF(B2="DAY OFF";TRUE;FALSE)
=IF(B2="23:00 - 07:00";TRUE;FALSE)
=IF(B2="15:00 - 23:00";TRUE;FALSE)
=IF(B2="07:00 - 15:00";TRUE;FALSE)

To paint the cell according to specific text.
Cell equals to "0" for each week total working time
Cell greater than 40 to paint red
a "today" function to highlight the current day i'm viewing the sheet

=NOT(ISERROR(VLOOKUP(E3;Holidays!$B:$B;1;0)))

to highlight all national holidays

=MONTH(E3)<>MONTH($E$9)

to gray out the parts of the month that is the previous or the next one. (this one is repeated 12 times. one for each month of the year...)
and

=MONTH(E3)=MONTH($E$9)

to highlight the actual days of the month (this one is also repeated 12 times, one for each month of the year)

Please help...

Work Schedule.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	MON		TUE		WED		THU		FRI		SAT		SUN
3											1/1/2022		2/1/2022		0
4											DAY OFF		15:00 - 23:00
5	3/1/2022		4/1/2022		5/1/2022		6/1/2022		7/1/2022		8/1/2022		9/1/2022		40
6	23:00 - 07:00		23:00 - 07:00		DAY OFF		DAY OFF		07:00 - 15:00		07:00 - 15:00		07:00 - 15:00
7	10/1/2022		11/1/2022		12/1/2022		13/1/2022		14/1/2022		15/1/2022		16/1/2022		40
8	07:00 - 15:00		07:00 - 15:00		15:00 - 23:00		15:00 - 23:00		DAY OFF		07:00 - 15:00		DAY OFF
9	17/1/2022		18/1/2022		19/1/2022		20/1/2022		21/1/2022		22/1/2022		23/1/2022		32
10	15:00 - 23:00		15:00 - 23:00		23:00 - 07:00		23:00 - 07:00		DAY OFF		DAY OFF		15:00 - 23:00
11	24/1/2022		25/1/2022		26/1/2022		27/1/2022		28/1/2022		29/1/2022		30/1/2022		40
12	15:00 - 23:00		23:00 - 07:00		DAY OFF		DAY OFF		07:00 - 15:00		07:00 - 15:00		07:00 - 15:00
13	31/1/2022														8
14	07:00 - 15:00
15	MON		TUE		WED		THU		FRI		SAT		SUN
16			1/2/2022		2/2/2022		3/2/2022		4/2/2022		5/2/2022		6/2/2022		0
17					23:00 - 07:00		DAY OFF		15:00 - 23:00		15:00 - 23:00		23:00 - 07:00
18	7/2/2022		8/2/2022		9/2/2022		10/2/2022		11/2/2022		12/2/2022		13/2/2022		0
19	23:00 - 07:00		23:00 - 07:00		DAY OFF		15:00 - 23:00		23:00 - 07:00		DAY OFF		15:00 - 23:00
20	14/2/2022		15/2/2022		16/2/2022		17/2/2022		18/2/2022		19/2/2022		20/2/2022		0
21	23:00 - 07:00		23:00 - 07:00		DAY OFF		DAY OFF		15:00 - 23:00		15:00 - 23:00		15:00 - 23:00
22	21/2/2022		22/2/2022		23/2/2022		24/2/2022		25/2/2022		26/2/2022		27/2/2022		0
23	23:00 - 07:00		23:00 - 07:00		DAY OFF		DAY OFF		15:00 - 23:00		15:00 - 23:00		15:00 - 23:00
24	28/2/2022														0
25	15:00 - 23:00		15:00 - 23:00
26															0
27
28	MON		TUE		WED		THU		FRI		SAT		SUN
29			1/3/2022		2/3/2022		3/3/2022		4/3/2022		5/3/2022		6/3/2022		0
30					15:00 - 23:00		DAY OFF		15:00 - 23:00		15:00 - 23:00		15:00 - 23:00
31	7/3/2022		8/3/2022		9/3/2022		10/3/2022		11/3/2022		12/3/2022		13/3/2022		0
32
33	14/3/2022		15/3/2022		16/3/2022		17/3/2022		18/3/2022		19/3/2022		20/3/2022		0
34	VACAY		VACAY		VACAY		VACAY		VACAY		VACAY		VACAY
35	21/3/2022		22/3/2022		23/3/2022		24/3/2022		25/3/2022		26/3/2022		27/3/2022		0
36
37	28/3/2022		29/3/2022		30/3/2022		31/3/2022								0
38
39															0
40
2023 (2)

Cell Formulas
Range	Formula
E3	E3	=DATE($C$15,$AB$3,1)-WEEKDAY(DATE($C$15,$AB$3,1),3)
G3,I3,K3,M3,O3,Q3,G29,I29,K29,M29,O29,Q29,G16,I16,K16,M16,O16,Q16	G3	=E3+1
E5,G5,I5,K5,M5,O5,Q5,E39,G39,I39,K39,M39,O39,E37,G37,I37,K37,M37,O37,Q37,E35,G35,I35,K35,M35,O35,Q35,E33,G33,I33,K33,M33,O33,Q33,E31,G31,I31,K31,M31,O31,Q31,E26,G26,I26,K26,M26,O26,E24,G24,I24,K24,M24,O24,Q24,E22,G22,I22,K22,M22,O22,Q22,E20,G20,I20,K20,M20	E5	=E3+7
S3,S5,S7,S9,S11,S13	S3	=AQ3
E16	E16	=DATE($C$15,$AB$4,1)-WEEKDAY(DATE($C$15,AB4,1),3)
S16,S18,S20,S22,S24,S26,S29,S31,S33,S35,S37,S39	S16	=AQ17
E29	E29	=DATE($C$15,$AB$5,1)-WEEKDAY(DATE($C$15,AB5,1),3)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4:R4,E6,E8,E10,E12,E14,E17,E19,E21,E23,E25,E27,E30,E32,E34,E36,E38,E40,E43,E45,E47,E49,E51,E53,E56,E58,E60,E62,E64,E66,Q12,O12,M12,K12,I12,G12,Q10,O10,M10,K10,I10,G10,Q8,O8,M8,K8,I8,G8,Q6,O6,M6,K6,I6,G6,Q14,O14,M14,K14,I14,G14,Q27,O27,M27,K27,I27,G27,G25	Expression	=IF(E4="";TRUE;FALSE)	text	NO
E4:R4,E6,E8,E10,E12,E14,E17,E19,E21,E23,E25,E27,E30,E32,E34,E36,E38,E40,E43,E45,E47,E49,E51,E53,E56,E58,E60,E62,E64,E66,Q12,O12,M12,K12,I12,G12,Q10,O10,M10,K10,I10,G10,Q8,O8,M8,K8,I8,G8,Q6,O6,M6,K6,I6,G6,Q14,O14,M14,K14,I14,G14,Q27,O27,M27,K27,I27,G27,G25	Expression	=IF(E4="VACAY";TRUE;FALSE)	text	NO
E4:R4,E6,E8,E10,E12,E14,E17,E19,E21,E23,E25,E27,E30,E32,E34,E36,E38,E40,E43,E45,E47,E49,E51,E53,E56,E58,E60,E62,E64,E66,Q12,O12,M12,K12,I12,G12,Q10,O10,M10,K10,I10,G10,Q8,O8,M8,K8,I8,G8,Q6,O6,M6,K6,I6,G6,Q14,O14,M14,K14,I14,G14,Q27,O27,M27,K27,I27,G27,G25	Expression	=IF(E4="DAY OFF";TRUE;FALSE)	text	NO
E4:R4,E6,E8,E10,E12,E14,E17,E19,E21,E23,E25,E27,E30,E32,E34,E36,E38,E40,E43,E45,E47,E49,E51,E53,E56,E58,E60,E62,E64,E66,Q12,O12,M12,K12,I12,G12,Q10,O10,M10,K10,I10,G10,Q8,O8,M8,K8,I8,G8,Q6,O6,M6,K6,I6,G6,Q14,O14,M14,K14,I14,G14,Q27,O27,M27,K27,I27,G27,G25	Expression	=IF(E4="23:00 - 07:00";TRUE;FALSE)	text	NO
E4:R4,E6,E8,E10,E12,E14,E17,E19,E21,E23,E25,E27,E30,E32,E34,E36,E38,E40,E43,E45,E47,E49,E51,E53,E56,E58,E60,E62,E64,E66,Q12,O12,M12,K12,I12,G12,Q10,O10,M10,K10,I10,G10,Q8,O8,M8,K8,I8,G8,Q6,O6,M6,K6,I6,G6,Q14,O14,M14,K14,I14,G14,Q27,O27,M27,K27,I27,G27,G25	Expression	=IF(E4="15:00 - 23:00";TRUE;FALSE)	text	NO
E4:R4,E6,E8,E10,E12,E14,E17,E19,E21,E23,E25,E27,E30,E32,E34,E36,E38,E40,E43,E45,E47,E49,E51,E53,E56,E58,E60,E62,E64,E66,Q12,O12,M12,K12,I12,G12,Q10,O10,M10,K10,I10,G10,Q8,O8,M8,K8,I8,G8,Q6,O6,M6,K6,I6,G6,Q14,O14,M14,K14,I14,G14,Q27,O27,M27,K27,I27,G27,G25	Expression	=IF(E4="07:00 - 15:00";TRUE;FALSE)	text	NO
S2:S157	Cell Value	>40	text	NO
S2:S157	Cell Value	=0	text	NO
E3:P3,E17,E5:R5,E7:R7,E9:R9,E11:R11,E13:R13,E18:R18,E20:R20,E22:R22,E26:P26,E24:R24,E30,E29:R29,E31:R31,E33:R33,E35:R35,E37:R37,E39:P39,E43,E42:R42,E44:R44,E46:R46,E48:R48,E50:R50,E52:P52,E56,E55:R55,E59:R59,E57:R57,E16:R16,E68:R68,E81:R81,E94:R94,E107:R107	Dates Occurring	today	text	NO
E5:R5,E7:R7,E9:R9,E11:R11,E13:R13,E3:Q3	Expression	=MONTH(E3)<>MONTH($E$9)	text	NO
E26:P26,E16:R16,E18:R18,E20:R20,E22:R22,E24:R24	Expression	=MONTH(E16)<>MONTH($E$22)	text	NO
E39:P39,E29:R29,E31:R31,E33:R33,E35:R35,E37:R37	Expression	=MONTH(E29)<>MONTH($E$33)	text	NO
E17,E5:R5,E7:R7,E9:R9,E11:R11,E13:R13,E18:R18,E20:R20,E22:R22,E26:P26,E24:R24,E30,E29:R29,E31:R31,E33:R33,E35:R35,E37:R37,E39:P39,E43,E42:R42,E44:R44,E46:R46,E48:R48,E50:R50,E52:P52,E56,E55:R55,E59:R59,E57:R57,E16:R16,E68:R68,E81:R81,E94:R94,E107:R107	Expression	=NOT(ISERROR(VLOOKUP(E3;Holidays!$B:$B;1;0)))	text	NO
E5:R5,E7:R7,E9:R9,E11:R11,E13:R13,E3:Q3	Expression	=MONTH(E3)=MONTH($E$9)	text	NO
E26:P26,E16:R16,E18:R18,E20:R20,E22:R22,E24:R24	Expression	=MONTH(E16)=MONTH($E$22)	text	NO
E39:P39,E29:R29,E31:R31,E33:R33,E35:R35,E37:R37	Expression	=MONTH(E29)=MONTH($E$33)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E34:R34	List	=$Y$15:$Y$20
E30:R30	List	=$Y$15:$Y$20
E32:R32	List	=$Y$15:$Y$20
E36:R36	List	=$Y$15:$Y$20
E38:R38	List	=$Y$15:$Y$20
E40:R40	List	=$Y$15:$Y$20

 

[Peter_SSs]

I'm also open to any recommendations of making this more... tidy...

I'm not sure that I would move away from Conditional Formatting, but you could simplify your Conditional Formatting formulas.

Instead of

=IF(B2="VACAY";TRUE;FALSE)

Just use

=B2="VACAY"

Instead of

=IF(E4="";TRUE;FALSE)

just use

=E4=""

etc