Excel 2002: Can I condition format block of cells ?

[Mr. Low]

Hello,

I have the following table:

A B C
1 Y12 XXXX XXXX - Yellow
2 Y12
3 T24 - Green
4 T24
5 T24
6 H85 - Pink
7 H85
8 J74 - Yellow
9 M41 - Green
10 M41
11 M41

Is there anyway for me to condition format the table as illustrated? The
block need to change color only when refrence in column A channges. How
should the formula be ? If 3 colors are not possible what about two ?

Thanks

Low

 

[Satti Charvak]

Please describe as to what is the condition of coloring a cell. anyways
conditional formating can be done for 3 colors (conditions), besides the
default color (normally white)

 

[ShaneDevenshire]

Hi,

1. Because Excel 2003 and earlier only support a max of 3 colors we need to
know what version of Excel you are using?
2. Which cells do you want to format with the color - the titles in column
A, items in the other columns if they contain entries? or the entire row with
titles and data area, or the whole data area without the titles but including
blank cells?
3. You show two areas in green, what determines why you color these two the
same and not use a different color? In other words what factor is
controlling your color coding? Maybe just color every other different item
an alternating color - green, yellow, green, yellow....

 

[T. Valko]

If 3 colors are not possible what about two ?

I can get you 2 colors.

Note that these formulas are fairly calculation intensive so this may not be
a good idea if you have 1000's of rows of data.

Based on your sample...

Select the range A1:C11
Goto the menu Format>Conditional Formatting
Condition 1
Select the Formula Is option
Enter this formula in the box on the right:
=MOD(ROUND(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=0
Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 2
Select the Formula Is option
Enter this formula in the box on the right:
=MOD(ROUND(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=1
Click the Format button
Select the desired style(s)
OK out

 

[T. Valko]

If 3 colors are not possible what about two ?

I can get you 2 colors.

Actually, you can get as many as conditional formatting will allow (which is
3 in versions of Excel prior to Excel 2007).

Just change the MOD divisor to the number of colors you want and change the
comparison accordingly:

For 3 colors:

=MOD(.....,3)=n

Condition 1:
=MOD(.....,3)=0

Condition 2:
=MOD(.....,3)=1

Condition 3:
=MOD(.....,3)=2

 

[Mr. Low]

Hello Valko,

Thanks for the formulas.

I needs to work on many rows of data perhaps 45,000, Is there any other
formula that could work well under this condition?

Thanks

Low

 

[T. Valko]

45k rows is too many for these formulas. Calculation would be extremely
slow.

The only other way that I can think of would use a helper column. Let's
assume your data looks like this:

...........A..........B
1.....header........
2........1.............
3........2.............
4........2.............
5........3.............
6........4.............
7........4.............

In B2 enter a "x".

In B3 enter this formula and copy down to the end of your data:

=IF(A3=A2,B2,IF(B2="x","y","x"))

You will end up with this:

...........A..........B
1.....header........
2........1...........x
3........2...........y
4........2...........y
5........3...........x
6........4...........y
7........4...........y

Then you can set 2 colors, one based on column B = x, the other based on
column B = y.

You can hide column B if you want, or you could use a column off to the
right of your data so that it's not displayed on the screen, say like column
AA.


--
Biff
Microsoft Excel MVP

Hello Valko,

Thanks for the formulas.

I needs to work on many rows of data perhaps 45,000, Is there any other
formula that could work well under this condition?

Thanks

Low

 

[Mr. Low]

Hello Valko,

I think this is a smart way.

I will try it out.

Thanks

Low

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Hello Valko,

I think this is a smart way.

I will try it out.

Thanks

Low

 

[fuoriclasse]

Hi guys!
I join the discussion a bit late but I had a similar problem and your
formula really helped. Many thanks T . Valko.

I am trying to understand the formula and I'm not sure what the
COUNTIF($A$1:$A1,$A$1:$A1) bit does.
Any chance you can enlight me?

In the sumproduct I understand the formula ($A$1:$A1<>""), you count the non
empty cells, but I dont really see whats happens after you divide by the
above.

Thanks

 

[jrg167]

Select the range A1:C11
Goto the menu Format>Conditional Formatting
Condition 1
Select the Formula Is option
Enter this formula in the box on the right:
=MOD(ROUND(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=0
Click the Format button
Select the desired style(s)
OK

Click the Add button

Condition 2
Select the Formula Is option
Enter this formula in the box on the right:
=MOD(ROUND(SUMPRODUCT(($A$1:$A1<>"")/COUNTIF($A$1:$A1,$A$1:$A1)),0),2)=1
Click the Format button
Select the desired style(s)
OK out

this did not work for me. THe first condition is always true. I can
see that the help cell would work, but that seems particularly ugly to
me. If only the color of a cell (fore, back, etc) was available via
a simple function. I guess I'll stick with just setting the first
cell of a new set of values to a different color, which is easy.