Highlight duplicates/groups different colors

  • Thread starter Thread starter whatever
  • Start date Start date
W

whatever

Hi i want to show , different color for each group i have 2003 excel
can i use conditional formatting?


123 green
123 green
456 red
456 red
888 green
999 red
999 red
 
I assume that your data is in Column-A. Place the cursor in A1 cell
and press Cntrl+Spacebar. Now the total column-A will be getting
selected and the A1 will be the Active cell (Active Cell will have a
white Background after selection).

Format>>Conditional Formatting>>Condition 1>>Formula Is>>
=COUNTIF(A:A,A1)=1
Then Click the Format command button and choose your desired Font and
Pattern colour.
This will highlight the Non Duplicate Values.

Press Add Command button to add another Condition
Condition 2>>Formula Is>>
=COUNTIF(A:A,A1)>1
Then Click the Format command button and choose your desired Font and
Pattern colour.
This will highlight the Duplicate Values.

Click Ok…

Hope it’s clear to you.
 
Hi thanks your solution ..i am aware of..it is not what i asked..
your solution ...does this

this is what i want i challenge anyone to do this in a conditional
format formula..? any gurus out there..?
 
whatever said:
Hi i want to show , different color for each group i have 2003 excel
can i use conditional formatting?


123 green
123 green
456 red
456 red
888 green
999 red
999 red
Click to expand...

Hello.

Try this:
A1 green, but conditionally red using the formula
=MOD(SUM(--(A1:A10<>A2:A11)),2)

The color sequence will start from the bottom,
but maybe you can tweak the formula so that it starts from the top, if desired.
I could not.

Hans T.
 
Hans Terkelsen said:
Hello.

Try this:
A1 green, but conditionally red using the formula
=MOD(SUM(--(A1:A10<>A2:A11)),2)

The color sequence will start from the bottom,
but maybe you can tweak the formula so that it starts from the top, if desired.
I could not.

Hans T.
Click to expand...

Hi again, forgot to mention that the format should be copied down,
using the format painter. Hans T.
 
Back
Top