Conditional formatting

[PeterSchmeits]

I am working on a planning sheet in Excel with each column representing a month and each row representing a development task. I used grouping to map several rows to the same topic (chapter, if you will) and thus created more layers. 

Per row I marked the months in which our company needs to work on the task. I would like to have the rows depicting the chapter/topic to be coloured based on whether or not one or more of the underlying cells are coloured. 

I attached an example. Row 4 is a chapter. Row 5 is a chapter within the chapter of row 4. Row 5 needs to be coloured if one or more cells in rows 6-10 are coloured. Row 4 needs to be coloured if one or more cells in row 5-38 are coloured. Row 20 needs to be coloured when one or more cells in row 21-24 are coloured, etc. 
Help is greatly appreciated.

Peter

[#2]

You need to approach this problem from a different angle. You can not use color as a basis for determining if it meets a conditional formatting formula. What you need to do instead is create a formula that will incorporate the sub-chapter criteria. Maybe if you supplied a sample sheet (no confidential/personal info) we could help figure out what that might look like.

[#3]

@mtarler 

Thank you for your reaction. I submit a sample sheet containing mock information. I started using colours for visualisation, but if text (for instance an X) is a better way to indicate that in that month we will work on that item, I am fine with that.

Thanks again. Hope you can do some magic

[#4 · Accepted solution]

@PeterSchmeits so I started down 1 path and got it to work but then thought of another option that is cleaner and easier.  In BOTH cases I add an "x" or "y" to the cells that you manually highlighted (which you should remove that manual color).  I used x and y because you had 2 colors and in theory could have multiple conditional formats to color differently accordingly but that gets tricky when you have both in the same chapter/subchapter.  The 2 versions then:

a) split the chapter, subchapter, ... names into their own columns and then I created a formula

b) I added a column that 'redefined' the chapter, subchapter, etc... into a standard table of contents style: 1.1.a.1, 1.1.a.2, etc...  It doesn't matter letters, numbers, roman numerals, etc... as long as all the corresponding sub-sections start with the parent id.  (NOTE: you have 1 issue because you have chapter 5 with subsection 1-11 and then define a chapter 5.1 with additional subsections.  So maybe I should have use 5.0.1 for the first group?)

See attached files and the conditional formatting inside each.

[#5]

@mtarler, thank you so much for this. I will have to do some shuffling, but this will work!