VBA cell formatting and index match replacement

Hi Everyone, hope you are having a blessed day!

I am trying to improve my holiday tracking workbook.
I would like VBA code to:

a) change cell interior colour if employee is not in employment (to affect K8:v30) to replace my existing conditional formatting
b) replace index match function to vba code (in K8:v30)

I am attaching sample workbook.

Formatting for a)
Currently i have simple conditional formatting

Please Login or Register  to view this content.

Formula for b) in cells K8:v30

Please Login or Register  to view this content.

is it something that is possible?
I am searching the web trying case vba, ranges but i am completely lost.

Thank you for your help in advance!

If you swap 0 and 1 in yr formula for conditional formatting it'll work fine.
But actually the formula could be just this:

Formula:

Please Login or Register  to view this content.

It's all abot the result being True (or 1)or False (or 0)

I'm not sure what you would like to improve in the formula for K8:V30.
Works fine, though I can imagine that you would like it to be more flexible or generic.
In that case I would focus on restructuring the M01, M02 etc sheets. E.g. converting the data ranges to tables would allow for referencing tables instead of sheetnames. Also no worries about rows and columns referencing.
I'm attaching an example of what I mean. Sheets M01 and M02 replaced by 1 sheet Data.

It really puzzles me why you would want the formulas replaced by vba