SOLVED

Conditional formatting

%3CLINGO-SUB%20id%3D%22lingo-sub-2856876%22%20slang%3D%22en-US%22%3EConditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2856876%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20working%20on%20a%20planning%20sheet%20in%20Excel%20with%20each%20column%20representing%20a%20month%20and%20each%20row%20representing%20a%20development%20task.%20I%20used%20grouping%20to%20map%20several%20rows%20to%20the%20same%20topic%20(chapter%2C%20if%20you%20will)%20and%20thus%20created%20more%20layers.%26nbsp%3B%3C%2FP%3E%3CP%3EPer%20row%20I%20marked%20the%20months%20in%20which%20our%20company%20needs%20to%20work%20on%20the%20task.%20I%20would%20like%20to%20have%20the%20rows%20depicting%20the%20chapter%2Ftopic%20to%20be%20coloured%20based%20on%20whether%20or%20not%20one%20or%20more%20of%20the%20underlying%20cells%20are%20coloured.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20attached%20an%20example.%20Row%204%20is%20a%20chapter.%20Row%205%20is%20a%20chapter%20within%20the%20chapter%20of%20row%204.%20Row%205%20needs%20to%20be%20coloured%20if%20one%20or%20more%20cells%20in%20rows%206-10%20are%20coloured.%20Row%204%20needs%20to%20be%20coloured%20if%20one%20or%20more%20cells%20in%20row%205-38%20are%20coloured.%20Row%2020%20needs%20to%20be%20coloured%20when%20one%20or%20more%20cells%20in%20row%2021-24%20are%20coloured%2C%20etc.%26nbsp%3B%3CBR%20%2F%3EHelp%20is%20greatly%20appreciated.%3C%2FP%3E%3CP%3EPeter%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2856876%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2858076%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2858076%22%20slang%3D%22en-US%22%3EYou%20need%20to%20approach%20this%20problem%20from%20a%20different%20angle.%20You%20can%20not%20use%20color%20as%20a%20basis%20for%20determining%20if%20it%20meets%20a%20conditional%20formatting%20formula.%20What%20you%20need%20to%20do%20instead%20is%20create%20a%20formula%20that%20will%20incorporate%20the%20sub-chapter%20criteria.%20Maybe%20if%20you%20supplied%20a%20sample%20sheet%20(no%20confidential%2Fpersonal%20info)%20we%20could%20help%20figure%20out%20what%20that%20might%20look%20like.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2859772%22%20slang%3D%22en-US%22%3ERe%3A%20Conditional%20formatting%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2859772%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20reaction.%20I%20submit%20a%20sample%20sheet%20containing%20mock%20information.%20I%20started%20using%20colours%20for%20visualisation%2C%20but%20if%20text%20(for%20instance%20an%20X)%20is%20a%20better%20way%20to%20indicate%20that%20in%20that%20month%20we%20will%20work%20on%20that%20item%2C%20I%20am%20fine%20with%20that.%3C%2FP%3E%3CP%3EThanks%20again.%20Hope%20you%20can%20do%20some%20magic%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

4 Replies
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.

@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

best response confirmed by allyreckerman (Microsoft)
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.

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