excel spreed sheet with

I have an excel sheet with 20 employees (employees name is in row D5 to AE5) . These employees work in either APAC or EMEA shift. The shift details of the employees is in row 16 from D16 to AE 16 These employees can avail leave from a list of holidays mentioned in column A( from A18 to A36). The employee must enter their leave by specifying the shift (APAC or EMEA) they are in the cell that corresponds to their name and the date they are planning to avail the leave from the given list of holidays. How can I automate to ensure that only 3 employees from each shift can avail the leave on a particular day (the respective cells must be highlighted in green) All other leave requests should be highlighted in red . If any of the three employees wishes to change the date of leave, the immediate next employee who keyed in the request should automatically be shown in green. How can I automate the above scenario using excel