Color cell by date

Hello,

I need to generate an attendance sheet and I have several Excel sheets, one which contains the attendance table and another which contains cells where you can add the departure date and the return date of an employee with a reason, the concern is that I can't get the departure date and the date just before the return as well as the days in between to be colored red with the reason written in the cell colored red.

The cells that will not be colored in the table should automatically be colored green.
Would it be possible to help me?
I have attached the file

Thank you.

Hello,
Well that's all good so I managed to do the rest, thank you very much. My problem was mainly the absences

thanks this helped me out as well