

April 23, 2015

Hi there,
I am trying to figure out how I can set the shading of a cell using conditional formatting based on the value of another cell. For example if I have the word "Lemon" in cell b2 how can I tell Excel to change the color of cell b3 to yellow?
Any help appreciated!
Alan

VIP

Trusted Members

June 25, 2016


VIP

Trusted Members

December 7, 2016

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


April 23, 2015

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

VIP

Trusted Members

December 7, 2016

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.


April 23, 2015

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
1 Guest(s)
