Excel 2002: Conditional format multiband colors

M

Mr. Low

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
 
B

Bob I

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
 
M

MyVeryOwnSelf

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top