Add advanced level condtional formatting rules

Hi,

I need help with creating a more advanced conditional formatting rule To best explain what I need please see the attached workbook. I have included a manually created mock sheet which shows what the final outcome should look like.

Thanks for looking!