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

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)
--
Kind Regards,
Satti Charvak
Only an Excel Enthusiast
Noida, India


"Mr. Low" wrote:

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

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....
--
Thanks,
Shane Devenshire


"Mr. Low" wrote:

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

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

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

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

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

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

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

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

On Nov 8, 9:57*am, "T. Valko" wrote:

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

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.