Color Excel Rows By Outline Level [closed]
I'm using the outline function in Excel to create an indented bill of materials (BOM) and I'd like to automatically color the rows by their outline level. A BOM is a list of parts in a product. For each sub assy in the BOM, it is typically indented to show the product structure and the Excel outline function works well for this.
What I'd like is for each level of the outline to be colored differently to aid in visualizing the BOM. I can't figure out how to capture the outline level in order to apply conditional formatting to make that happen.
Frankly, I'm after just what is described in this question, except my outline is by row instead of by column. I have not been successful in getting the solution offered there to work and haven't found another solution here or elsewhere online. I think there are assumptions made there that I don't understand or I'm not implementing the custom function properly. I'm not permitted to comment on that solution for clarification, which is why I posted a new question.
Can anyone help me get that solution to work or offer an alternative?
Thank you.
excel
asked Nov 26 '18 at 19:50
salguodsalguod
12
closed as too broad by pnuts, ewolden, Machavity, NathanOliver, Vega Nov 27 '18 at 14:21
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
I'm using the outline function in Excel to create an indented bill of materials (BOM) and I'd like to automatically color the rows by their outline level. A BOM is a list of parts in a product. For each sub assy in the BOM, it is typically indented to show the product structure and the Excel outline function works well for this.
What I'd like is for each level of the outline to be colored differently to aid in visualizing the BOM. I can't figure out how to capture the outline level in order to apply conditional formatting to make that happen.
Frankly, I'm after just what is described in this question, except my outline is by row instead of by column. I have not been successful in getting the solution offered there to work and haven't found another solution here or elsewhere online. I think there are assumptions made there that I don't understand or I'm not implementing the custom function properly. I'm not permitted to comment on that solution for clarification, which is why I posted a new question.
Can anyone help me get that solution to work or offer an alternative?
Thank you.
excel
asked Nov 26 '18 at 19:50
salguodsalguod
12
closed as too broad by pnuts, ewolden, Machavity, NathanOliver, Vega Nov 27 '18 at 14:21
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
I'm using the outline function in Excel to create an indented bill of materials (BOM) and I'd like to automatically color the rows by their outline level. A BOM is a list of parts in a product. For each sub assy in the BOM, it is typically indented to show the product structure and the Excel outline function works well for this.
What I'd like is for each level of the outline to be colored differently to aid in visualizing the BOM. I can't figure out how to capture the outline level in order to apply conditional formatting to make that happen.
Frankly, I'm after just what is described in this question, except my outline is by row instead of by column. I have not been successful in getting the solution offered there to work and haven't found another solution here or elsewhere online. I think there are assumptions made there that I don't understand or I'm not implementing the custom function properly. I'm not permitted to comment on that solution for clarification, which is why I posted a new question.
Can anyone help me get that solution to work or offer an alternative?
Thank you.
excel
asked Nov 26 '18 at 19:50
salguodsalguod
12
I'm using the outline function in Excel to create an indented bill of materials (BOM) and I'd like to automatically color the rows by their outline level. A BOM is a list of parts in a product. For each sub assy in the BOM, it is typically indented to show the product structure and the Excel outline function works well for this.
What I'd like is for each level of the outline to be colored differently to aid in visualizing the BOM. I can't figure out how to capture the outline level in order to apply conditional formatting to make that happen.
Frankly, I'm after just what is described in this question, except my outline is by row instead of by column. I have not been successful in getting the solution offered there to work and haven't found another solution here or elsewhere online. I think there are assumptions made there that I don't understand or I'm not implementing the custom function properly. I'm not permitted to comment on that solution for clarification, which is why I posted a new question.
Can anyone help me get that solution to work or offer an alternative?
Thank you.
excel
excel
asked Nov 26 '18 at 19:50
salguodsalguod
12
asked Nov 26 '18 at 19:50
salguodsalguod
12
edited Nov 28 '18 at 22:30
salguod
asked Nov 26 '18 at 19:50
salguodsalguod
12
asked Nov 26 '18 at 19:50
salguodsalguod
12
asked Nov 26 '18 at 19:50
salguodsalguod
12
12
closed as too broad by pnuts, ewolden, Machavity, NathanOliver, Vega Nov 27 '18 at 14:21
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
closed as too broad by pnuts, ewolden, Machavity, NathanOliver, Vega Nov 27 '18 at 14:21
Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
I found a partial answer. It's not quite what I want, but it's an improvement.
This section of the Office Support article on outlines describes how to apply styles to an outline. I had tried it, but the built in styles only change levels one and two and then only apply bold and italics. I simply didn't see the changes. By modifying the built in styles, they became more distinct.
That said, it only applies those styles to the lead line at each level, not to every item at each level:
Auto Styles
So, while it does help, it's not exactly what I want. I'd really like every line to have formatting corresponding to its level in the outline. As you can see in the image above, it's not easy to see where an outline level stops using this auto formatting.
answered Nov 27 '18 at 13:11
salguodsalguod
12
add a comment |
This is a better solution, but I'll leave the other because it may help someone else.
Post #4 in this thread at Mr. Excel describes how to report the outline level in a cell. It's old, so the menu picks are different. You get to the Define name dialog by selecting the cell, right mouse and pick Define Name from the pop up menu.
Now that I have the outline level in a cell, I was able to use regular conditional formatting rules to format the worksheet:
Fully Colored
This does require you to save it as a macro enabled workbook.
answered Nov 27 '18 at 14:14
salguodsalguod
12
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
I found a partial answer. It's not quite what I want, but it's an improvement.
This section of the Office Support article on outlines describes how to apply styles to an outline. I had tried it, but the built in styles only change levels one and two and then only apply bold and italics. I simply didn't see the changes. By modifying the built in styles, they became more distinct.
That said, it only applies those styles to the lead line at each level, not to every item at each level:
Auto Styles
So, while it does help, it's not exactly what I want. I'd really like every line to have formatting corresponding to its level in the outline. As you can see in the image above, it's not easy to see where an outline level stops using this auto formatting.
answered Nov 27 '18 at 13:11
salguodsalguod
12
add a comment |
I found a partial answer. It's not quite what I want, but it's an improvement.
This section of the Office Support article on outlines describes how to apply styles to an outline. I had tried it, but the built in styles only change levels one and two and then only apply bold and italics. I simply didn't see the changes. By modifying the built in styles, they became more distinct.
That said, it only applies those styles to the lead line at each level, not to every item at each level:
Auto Styles
So, while it does help, it's not exactly what I want. I'd really like every line to have formatting corresponding to its level in the outline. As you can see in the image above, it's not easy to see where an outline level stops using this auto formatting.
answered Nov 27 '18 at 13:11
salguodsalguod
12
add a comment |
I found a partial answer. It's not quite what I want, but it's an improvement.
This section of the Office Support article on outlines describes how to apply styles to an outline. I had tried it, but the built in styles only change levels one and two and then only apply bold and italics. I simply didn't see the changes. By modifying the built in styles, they became more distinct.
That said, it only applies those styles to the lead line at each level, not to every item at each level:
Auto Styles
So, while it does help, it's not exactly what I want. I'd really like every line to have formatting corresponding to its level in the outline. As you can see in the image above, it's not easy to see where an outline level stops using this auto formatting.
answered Nov 27 '18 at 13:11
salguodsalguod
12
I found a partial answer. It's not quite what I want, but it's an improvement.
This section of the Office Support article on outlines describes how to apply styles to an outline. I had tried it, but the built in styles only change levels one and two and then only apply bold and italics. I simply didn't see the changes. By modifying the built in styles, they became more distinct.
That said, it only applies those styles to the lead line at each level, not to every item at each level:
Auto Styles
So, while it does help, it's not exactly what I want. I'd really like every line to have formatting corresponding to its level in the outline. As you can see in the image above, it's not easy to see where an outline level stops using this auto formatting.
answered Nov 27 '18 at 13:11
salguodsalguod
12
answered Nov 27 '18 at 13:11
salguodsalguod
12
answered Nov 27 '18 at 13:11
salguodsalguod
12
answered Nov 27 '18 at 13:11
salguodsalguod
12
12
add a comment |
add a comment |
This is a better solution, but I'll leave the other because it may help someone else.
Post #4 in this thread at Mr. Excel describes how to report the outline level in a cell. It's old, so the menu picks are different. You get to the Define name dialog by selecting the cell, right mouse and pick Define Name from the pop up menu.
Now that I have the outline level in a cell, I was able to use regular conditional formatting rules to format the worksheet:
Fully Colored
This does require you to save it as a macro enabled workbook.
answered Nov 27 '18 at 14:14
salguodsalguod
12
add a comment |
This is a better solution, but I'll leave the other because it may help someone else.
Post #4 in this thread at Mr. Excel describes how to report the outline level in a cell. It's old, so the menu picks are different. You get to the Define name dialog by selecting the cell, right mouse and pick Define Name from the pop up menu.
Now that I have the outline level in a cell, I was able to use regular conditional formatting rules to format the worksheet:
Fully Colored
This does require you to save it as a macro enabled workbook.
answered Nov 27 '18 at 14:14
salguodsalguod
12
add a comment |
This is a better solution, but I'll leave the other because it may help someone else.
Post #4 in this thread at Mr. Excel describes how to report the outline level in a cell. It's old, so the menu picks are different. You get to the Define name dialog by selecting the cell, right mouse and pick Define Name from the pop up menu.
Now that I have the outline level in a cell, I was able to use regular conditional formatting rules to format the worksheet:
Fully Colored
This does require you to save it as a macro enabled workbook.
answered Nov 27 '18 at 14:14
salguodsalguod
12
This is a better solution, but I'll leave the other because it may help someone else.
Post #4 in this thread at Mr. Excel describes how to report the outline level in a cell. It's old, so the menu picks are different. You get to the Define name dialog by selecting the cell, right mouse and pick Define Name from the pop up menu.
Now that I have the outline level in a cell, I was able to use regular conditional formatting rules to format the worksheet:
Fully Colored
This does require you to save it as a macro enabled workbook.
answered Nov 27 '18 at 14:14
salguodsalguod
12
answered Nov 27 '18 at 14:14
salguodsalguod
12
answered Nov 27 '18 at 14:14
salguodsalguod
12
answered Nov 27 '18 at 14:14
salguodsalguod
12
12
add a comment |
add a comment |
