Conditional formatting based off two variables - SOLVED

Copper Contributor

Hi! My first time posting :)

I have a complex Gant chart used for a marketing plan. Each channel (TV ect) connects to a tab and the x's on the page are a formula picking up from another tab when there activity planned for that week. 


I want the x's to highlight different colours pending the campaign status. C8 is where the campaign status is listed and is a drop down list. I want the colour coding to be red if Planned and navy when booked ect. 


My thoughts are to use a formula in conditional formatting to say IF $D$17:$BC$39 has an 'x' AND C8 says PLANNED, turn Red. IF $D$17:$BC$39 has an 'x' AND C8 says BOOKED, turn Blue ect.


Is this even possible? 


Screen Shot 2022-07-20 at 4.46.58 pm.png

Thank you very much! 





4 Replies
best response confirmed by SamMitchell515 (Copper Contributor)

Hi @SamMitchell515 


yes, that's possible:


First, highlight the whole range where the conditional formatting needs to be applied. In my example D17:H19

Then add a rule for every status, like this:




Hi @Martin_Weiss 

Thank you very much for your response. I tried your suggestions it's saying there is a problem with the formula: 

Screen Shot 2022-07-21 at 9.38.00 am.png

This is what I put in the formula bar:

Screen Shot 2022-07-21 at 9.37.50 am.png

Am I doing something wrong? 



Hi @SamMitchell515,


most likely you just need to replace ; with , in your formula. This is due to regional settings (in Germany, we have ; as a separator)

So try :


instead of


It worked!! Thank you very much Martin, I have been stewing over this forumla for a long time! haha