Selective Highlighting Of Leaves Holiday In Calendar Through Conditional Format

[Excel Fox]

Here's a way of doing it using Conditional Format

Create named ranges

EmployeeList =OFFSET('Leave Dates'!$B$1,1,0,COUNTA('Leave Dates'!$B$2:$B$30),1)
StartDate =OFFSET('Leave Dates'!$C$1,1,0,COUNTA('Leave Dates'!$B$2:$B$30),1)
EndDate =OFFSET('Leave Dates'!$D$1,1,0,COUNTA('Leave Dates'!$B$2:$B$30),1)

Create a validation list somewhere in the calendar sheet, say in AH11

Create named range

SelectedEmployee ='Leave Schedule'!$AH$11

The select the entire calendar starting from B13 and use the formula section of conditional formatting with the following formula

=(B13>=INDEX(StartDate,MATCH(SelectedEmployee,Empl oyeeList,0)))*(B13<=INDEX(EndDate,MATCH(SelectedEm ployee,EmployeeList,0)))

Attached working file for reference....