Selective Highlighting Of Leaves Holiday In Calendar Through Conditional Format

[msiyab]

Hello Guys,

I would like to know how to perform selective highlighting in excel.
Basically, I have a calendar in excel and besides that are names of employees of my dept. So i need some formula or VBA or anything that highlights the annual leave days on the calendar when I click on a employees name.

Sheet 1 is the 2013 calender and besides it are the names of the employee (when clicked needs to highlight their respective leave days), Sheet 2 contains the names of employees and their leave dates. Please note, i will change the names in Sheet 2 according to the departments.

Please find the attached workbook...

Thanks...

[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,EmployeeLi st,0)))*(B13<=INDEX(EndDate,MATCH(SelectedEmployee,EmployeeList ,0)))

Attached working file for reference....

[msiyab]

Thanks for the help!! You guys are just great.

However, i aint that advanced to understand what you wrote with the coding above. But the file does its job nonetheless.

I would like to know though, how did you make the drop down list next to the calendar?