Nested conditional formatting challenge

Hi Everyone,

Firstly, I'm not sure this is actually possible.

I'm trying to build a dynamic gantt chart which will automatically populate the shaded boxes with colour based on two cells containing the date range, date from (x) date to (y) (the easy bit).

The difficulty comes because I'm trying to base the colour of the shaded cell range on a third cell for each row (z). Meaning a project manager could fill in X and Y to get the range, and choose the colour of cell z to dictate the colour of the highlighting/shading on the gantt chart. If it makes it possible, I'm happy to choose to limit the colours from a prearranged range, but if possible, I'd prefer not to.

Is this possible without using VBA? (Simply because I have zero experience, as do most/all of my PM's.)

Any help would be really appreciated!

Thanks again,
Harry

Hello and welcome to the forum.

If I am understanding you correctly, you are looking to change the fill color of a cell and have the color of the chart change to the color of that cell?

You cannot use formulas (and therefore not Conditional Formatting) to copy a cell color. Only VBA can "see" the fill color of a cell.

This would be possible if, for example, you were to type in "Green", "Red", or "Yellow" into that cell. Then you would need one Conditional Formatting formula per color.

Hi Falcon,

Thanks for your help. Totally understand in which case, if (z) in my example, were to be a dropdown list of words (say Green, Blue, Red, Grey, Purple) etc, of say 5 colours, could I then have the conditional formatting for the gantt chart pick up the word used and format the cells within the date range? (I hope I understood you correctly).

If that's the way to go about it, I'm happy to build that in.

Thanks again,
Harry

Absolutely. Create a small representative sample of your workbook along with manually filling in the cells that the Conditional Formatting should pick up. I'll get the CF working with multiple colors once I have your layout in front of me.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.