Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Conditional format multiband colors
Hi,
I need to conditional format a table with 3 rotating colors of yellow, green and blue. The condition to change the color is when the code changes. Illustration: A B C D 1 B12 XXX XXX Yellow 2 B12 3 C26 Green 4 C26 5 C26 6 D52 Blue 7 E27 Yellow 8 E27 9 E27 10 E27 11 G49 Green 12 G49 13 H96 Blue There is a maximum of 3 formulas under conditional formating. May I know what are the formulas 1, 2 and 3 in order for me to get the result ? Thanks Low -- A36B58K641 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Conditional format multiband colors
Conditional formatting is based on the outcome of the condition
evaluating to True. If the evaluation is True, then the specific color you selected is applied. i believe if you want this color scheme then you will need to use VBA to effect the rotating colors Mr. Low wrote: Hi, I need to conditional format a table with 3 rotating colors of yellow, green and blue. The condition to change the color is when the code changes. Illustration: A B C D 1 B12 XXX XXX Yellow 2 B12 3 C26 Green 4 C26 5 C26 6 D52 Blue 7 E27 Yellow 8 E27 9 E27 10 E27 11 G49 Green 12 G49 13 H96 Blue There is a maximum of 3 formulas under conditional formating. May I know what are the formulas 1, 2 and 3 in order for me to get the result ? Thanks Low |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Conditional format multiband colors
I need to conditional format a table with 3 rotating colors of yellow,
green and blue. The condition to change the color is when the code changes. A B C D 1 B12 XXX XXX Yellow 2 B12 3 C26 Green 4 C26 5 C26 6 D52 Blue 7 E27 Yellow 8 E27 9 E27 10 E27 11 G49 Green 12 G49 13 H96 Blue May I know what are the formulas 1, 2 and 3 in order for me to get the result ? One way is to use a helper column. For example, start by putting 0 (zero) in E1. Then put something like this in E2 =IF(A2="",-1,IF(A2=A1,E1,MOD(E1+1,3))) and copy down as far as the list might ever reach. This gives numbers 0, 1, and 2 for the three different row colors. I put in the -1 presuming that rows after the end of the list are supposed to have no color. The helper column can be hidden (or kept out of the print range) to avoid clutter. Modify to suit. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002: Conditional format multiband colors
Hi,
Thanks for your formula. It works. Best Regards Low -- A36B58K641 "MyVeryOwnSelf" wrote: I need to conditional format a table with 3 rotating colors of yellow, green and blue. The condition to change the color is when the code changes. A B C D 1 B12 XXX XXX Yellow 2 B12 3 C26 Green 4 C26 5 C26 6 D52 Blue 7 E27 Yellow 8 E27 9 E27 10 E27 11 G49 Green 12 G49 13 H96 Blue May I know what are the formulas 1, 2 and 3 in order for me to get the result ? One way is to use a helper column. For example, start by putting 0 (zero) in E1. Then put something like this in E2 =IF(A2="",-1,IF(A2=A1,E1,MOD(E1+1,3))) and copy down as far as the list might ever reach. This gives numbers 0, 1, and 2 for the three different row colors. I put in the -1 presuming that rows after the end of the list are supposed to have no color. The helper column can be hidden (or kept out of the print range) to avoid clutter. Modify to suit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2002: How to conditional format band of colors ? | Excel Discussion (Misc queries) | |||
colors and conditional format - greater than, less than | Excel Discussion (Misc queries) | |||
Excel 2002 : How to conditional format the Sub Total Table ? | Excel Discussion (Misc queries) | |||
Excel 2002: How to conditional format a row of cells ? | Excel Discussion (Misc queries) | |||
Cell colors on conditional format | Excel Discussion (Misc queries) |