Hi Alan

Please refer attachment.

Sunny

Hello Alan,

Just providing a link to some more information about conditional formatting using formulas.

https://www.myonlinetraininghub.com/excel-conditional-formatting-with-formulas

SunnyKow/Anders,

Many thanks for the help - that's a good start for me.  I am going to try an incorporate some "If's" into the formula.  Will let you know if I am struggling!

Alan 

Hi there,

I did say I would post again if I was struggling and I am 🙂 

In the attached file you will see that I am trying to generate a gantt style work sheet.  The date and durations entered in columns T and U define which cells are shaded under columns W onward. I am trying to figure out how I can change color of those shaded cells to a unique color based on the value selected in column H (Project).  So if the user selects "IOL D40" the shaded cells from BD-BZ will be say purple, if the user selects "Shell-Groundbirch ISM" from column H those same cells will change from purple to say orange etc.

Can anyone help me with this?

Thanks in advance,

Alan

Hello,

That is a lot of conditional formatting rules you have in your workbook. It seems to me that you know how to set up such rule you want help with. You have one such rule already, setting red background when =$H$2="IOL D40". That rule is applied to following ranges, =$LQ$3:$NN$3;$W$2:$NN$2;$OB$19:$SH$19;$NY$20:$SH$35;$NY$2:$SH$18;$W$4:$NN$5;$LS$36:$SH$68;$LS$6:$NN$35, which seems very spread out in my opinion.

I am really not sure what it is you want help with.

Thanks Anders,

I apologize for my request being a bit confusing, I guess I was hoping there might be an "easier" way to get the effect I am after without having to enter so many conditions.  The project entries I would be using in the conditional rules will change over time, which would mean me having to amend the conditional formulas to reflect new projects getting added and older ones dropping off.  All of that is possible of course, just a bit long winded.

Thanks for taking the time to look at the problem and give me your feedback, sometimes its just as good to know there isn't a "magic bullet".  I'll carry on with the current approach and see just how much it turns out to be to maintain.

Alan