Conditional Format Prioritization

[Nicvic]

I am trying to format cells based on dates, with the first priority being the first rule "green" then simply color coding the rest of the cells based on dates. I keep getting cells highlighted with rules that don't meet the criteria in the original cell. For example, Cell H9 should be Green since the column "Task complete" has the word Yes, however it is showing "Past Due" in orange. Cell H7 shows Orange, even though there is no date in the cell. How do I set the priority of Green / Task Complete, Orange/ Past Due, Red/This Week, Gold/Next Week, Yellow/ More than 15 Days out, and no format for blank cells.

 

[Fluff]

Change the $L6 to $L7 the formula should match the first row in the applies to range.

 

[Nicvic]

Thank you!

 

[Fluff]

You're welcome.