You're welcome!

--
Biff
Microsoft Excel MVP


"Mr. Low" wrote in message
...
Hello Valko,

I think this is a smart way.

I will try it out.

Thanks

Low

--
A36B58K641


"T. Valko" wrote:

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


"Mr. Low" wrote in message
...
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

--
A36B58K641


"T. Valko" wrote:

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


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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 FormatConditional 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


--
Biff
Microsoft Excel MVP


"Mr. Low" wrote in message
...
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




--
A36B58K641