Conditional Formatting for Gant Chart based on : Start Date / End Date / Status

Hello and nice to meet you all !

A good friend of mine recommended this website and suggested that here all the brightest and modest minds gather to help solve together all kinds of problems that we encounter in our daily excel based activities.

My Role : Junior PM
Excel Level : Basic
Excel Usage: Gant Charts
Excel Formula used : =IF(AND(K$3>=$E4,K$3<=$F4),"x","") - helps display with "x" the days that a task is being worked on.
Issue : I want to use conditional formatting to highlight (Green, Yellow, Gray, Red ) tasks that have the following status : Done / WIP / Not Started / Blocked.

Edit about usage: The scope would be to paste the formatting on all blue cells in order to automate the colors and "x" spots each time the tasks enter different status.
Task count: 50 - 100

Attached you will find the test sheet that I am working for, if you could also help me find a way to drag/use the formatting on multiple columns and rows that will be much appreciated.

Excel Issue.JPG

Best regards,
Razvan

Without a sample spreadsheet to test from (can't test on a picture), untested, try this...

Conditional Formatting

• Highlight applicable range >> E4:AS7
• Home Tab >> Styles >> Conditional Formatting >> New Rule
• Select a Rule Type: Use a formula to determine which cells to format
• Edit the Rule Description: Format values where this formula is true: =AND($D4="Done",E$3>=$B4,E$3<=$C4)
• Format… [Number, Font, Border, Fill] >> you need to make one of these conditions for every color choice
• OK >> OK

Here's the file I worked on...