Vacation Plan with Gantt Chart

Dear Gurus,
I have one excel worksheet divided in two sections for vacation plan for employees. First portion is Gantt Chart and the second part is Segregated leave plan as per dates. Sample sheet is attached herewith. My task is whenever an employees is added in the second section for his vacation start date and end date, I would like excel to automatically add employee number and names and highlight the respective cells in gantt chart form in the first section. I have around 113 employees. I tried with sumif and sumproduct but in vain. If any body can help it would be highly appreciated.
Thanks for the help in advance