From Conditional Formatting to VBA

Agnarr

New Member
Joined
Jan 15, 2023
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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:
Excel Formula:
=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
Excel Formula:
=NOT(ISERROR(VLOOKUP(E3;Holidays!$B:$B;1;0)))
to highlight all national holidays

Excel Formula:
=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
Excel Formula:
=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
EFGHIJKLMNOPQRS
2MONTUEWEDTHUFRISATSUN
3     1/1/20222/1/20220
4DAY OFF15:00 - 23:00
53/1/20224/1/20225/1/20226/1/20227/1/20228/1/20229/1/202240
623:00 - 07:0023:00 - 07:00DAY OFFDAY OFF07:00 - 15:0007:00 - 15:0007:00 - 15:00
710/1/202211/1/202212/1/202213/1/202214/1/202215/1/202216/1/202240
807:00 - 15:0007:00 - 15:0015:00 - 23:0015:00 - 23:00DAY OFF07:00 - 15:00DAY OFF
917/1/202218/1/202219/1/202220/1/202221/1/202222/1/202223/1/202232
1015:00 - 23:0015:00 - 23:0023:00 - 07:0023:00 - 07:00DAY OFFDAY OFF15:00 - 23:00
1124/1/202225/1/202226/1/202227/1/202228/1/202229/1/202230/1/202240
1215:00 - 23:0023:00 - 07:00DAY OFFDAY OFF07:00 - 15:0007:00 - 15:0007:00 - 15:00
1331/1/2022      8
1407:00 - 15:00
15MONTUEWEDTHUFRISATSUN
16 1/2/20222/2/20223/2/20224/2/20225/2/20226/2/20220
1723:00 - 07:00DAY OFF15:00 - 23:0015:00 - 23:0023:00 - 07:00
187/2/20228/2/20229/2/202210/2/202211/2/202212/2/202213/2/20220
1923:00 - 07:0023:00 - 07:00DAY OFF15:00 - 23:0023:00 - 07:00DAY OFF15:00 - 23:00
2014/2/202215/2/202216/2/202217/2/202218/2/202219/2/202220/2/20220
2123:00 - 07:0023:00 - 07:00DAY OFFDAY OFF15:00 - 23:0015:00 - 23:0015:00 - 23:00
2221/2/202222/2/202223/2/202224/2/202225/2/202226/2/202227/2/20220
2323:00 - 07:0023:00 - 07:00DAY OFFDAY OFF15:00 - 23:0015:00 - 23:0015:00 - 23:00
2428/2/2022      0
2515:00 - 23:0015:00 - 23:00
26      0
27
28MONTUEWEDTHUFRISATSUN
29 1/3/20222/3/20223/3/20224/3/20225/3/20226/3/20220
3015:00 - 23:00DAY OFF15:00 - 23:0015:00 - 23:0015:00 - 23:00
317/3/20228/3/20229/3/202210/3/202211/3/202212/3/202213/3/20220
32
3314/3/202215/3/202216/3/202217/3/202218/3/202219/3/202220/3/20220
34VACAYVACAYVACAYVACAYVACAYVACAYVACAY
3521/3/202222/3/202223/3/202224/3/202225/3/202226/3/202227/3/20220
36
3728/3/202229/3/202230/3/202231/3/2022   0
38
39      0
40
2023 (2)
Cell Formulas
RangeFormula
E3E3=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,Q16G3=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,M20E5=E3+7
S3,S5,S7,S9,S11,S13S3=AQ3
E16E16=DATE($C$15,$AB$4,1)-WEEKDAY(DATE($C$15,AB4,1),3)
S16,S18,S20,S22,S24,S26,S29,S31,S33,S35,S37,S39S16=AQ17
E29E29=DATE($C$15,$AB$5,1)-WEEKDAY(DATE($C$15,AB5,1),3)
Cells with Conditional Formatting
CellConditionCell FormatStop 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,G25Expression=IF(E4="";TRUE;FALSE)textNO
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,G25Expression=IF(E4="VACAY";TRUE;FALSE)textNO
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,G25Expression=IF(E4="DAY OFF";TRUE;FALSE)textNO
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,G25Expression=IF(E4="23:00 - 07:00";TRUE;FALSE)textNO
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,G25Expression=IF(E4="15:00 - 23:00";TRUE;FALSE)textNO
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,G25Expression=IF(E4="07:00 - 15:00";TRUE;FALSE)textNO
S2:S157Cell Value>40textNO
S2:S157Cell Value=0textNO
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:R107Dates OccurringtodaytextNO
E5:R5,E7:R7,E9:R9,E11:R11,E13:R13,E3:Q3Expression=MONTH(E3)<>MONTH($E$9)textNO
E26:P26,E16:R16,E18:R18,E20:R20,E22:R22,E24:R24Expression=MONTH(E16)<>MONTH($E$22)textNO
E39:P39,E29:R29,E31:R31,E33:R33,E35:R35,E37:R37Expression=MONTH(E29)<>MONTH($E$33)textNO
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:R107Expression=NOT(ISERROR(VLOOKUP(E3;Holidays!$B:$B;1;0)))textNO
E5:R5,E7:R7,E9:R9,E11:R11,E13:R13,E3:Q3Expression=MONTH(E3)=MONTH($E$9)textNO
E26:P26,E16:R16,E18:R18,E20:R20,E22:R22,E24:R24Expression=MONTH(E16)=MONTH($E$22)textNO
E39:P39,E29:R29,E31:R31,E33:R33,E35:R35,E37:R37Expression=MONTH(E29)=MONTH($E$33)textNO
Cells with Data Validation
CellAllowCriteria
E34:R34List=$Y$15:$Y$20
E30:R30List=$Y$15:$Y$20
E32:R32List=$Y$15:$Y$20
E36:R36List=$Y$15:$Y$20
E38:R38List=$Y$15:$Y$20
E40:R40List=$Y$15:$Y$20
 

Attachments

  • Καταγραφή.JPG
    Καταγραφή.JPG
    205.1 KB · Views: 2

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
Excel Formula:
=IF(B2="VACAY";TRUE;FALSE)
Just use
Excel Formula:
=B2="VACAY"

Instead of
Excel Formula:
=IF(E4="";TRUE;FALSE)
just use
Excel Formula:
=E4=""

etc
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top