Formulas for staff leave tracker and perpetual calendar

Hi, I am trying to modify a Microsoft template (see attached) to track staff leave and travel. I want to use conditional formatting to highlight what leave employees are taking and be able to see all staff leave on one sheet, divided into teams. I have started to setup the conditional formatting but with 12 different leave types and about 50 staff the method I am using is not effective and will break if staff leave or new staff join. So my issues are

1. What formula can I use to lookup the leave input sheet by employee then conditionally format the cell corresponding to the employee on the calendar ?
2. How do I make the month drop down box work with the year drop down box so it doesn't break the calendar ?

Any help greatly appreciated.

Anyone ???

The formula I am currently using for the conditional formatting is

=(MONTH(H15)=MONTH($C15))*(COUNTIFS(lstEmpNames,"*Brown, Steve*",lstSdates,"<="&H15,lstEDates,">="&H15,lstHTypes,Settings!$H$4)>0)

Heya Phil did you get an answer to this issue? please can you share with me [email protected]