Conditional Formatting?

[Ken]

Excel 2000 ... I have random PNs in Col B that are not
unique ... They are sorted (Ascend) & often repeat ...

Above said ... I would like to set Conditional Formatting
to change pattern color when PN changes.

123456 ... Yellow
234567 ... Green
234567 ... Green
234567 ... Green
345678 ... Yellow
345678 ... Yellow
456789 ... Green
567890 ... Yellow
567890 ... Yellow
etc ...

As always ... My many Thanks to those of you that are
intimate with Excel & support this board ... Kha

 

[Frank Kabel]

Hi
try:
- select your range (e.g. A1:A20)
- goto the conditional format dialog and choose 'Formula is'
- enter the following formula
=mod(SUMPRODUCT(($A$1:$A1<>"")/(COUNTIF($A$1:$A1,$A$1:$A1)+($A$1:$A1=""))),2)=1
- choose the color "Yellow"
- add a new condition and enter the following formula
=mod(SUMPRODUCT(($A$1:$A1<>"")/(COUNTIF($A$1:$A1,$A$1:$A1)+($A$1:$A1=""))),2)=0
- choose the 'Green' color

 

[Ken]

Good morning Frank (at least here) ... Since my Range
started with A3 I replaced A1 in your Formula with A3, but
I am not getting close to the desired results??? Usually,
any oversights are on my part from the explanation side
(as I am always confident that if I explain it right that
you will get it right from the Excel side).

Above said ...

PNs are in Range A3:A600 ... PNs are sorted & may or may
not be unique ... Sometimes PNs repeat a number of times &
sometimes they are unique. For each NEW PN I want to
change the Cell Pattern color. The default "No Fill"
pattern color is fine for every other PN ...

Hope this better describes issue or Please tell me where I
fell down in writing your formula ... Thanks Frank ...
Your help & the help of the other Magicians that support
this board is always appreciated ... Kha

 

[Frank Kabel]

Hi
post the exact formula you have used and also state which cells you had
selected

 

[Frank Kabel]

Hi
formulas should do. If you like email me an example sheet with the
desired result manually applied and I'll have a look at it

 

[Ken]

Frank ... (Hi)

A clarifier ... my PNs are Alpha-numeric & may contain
dashes (-) ... 123456-01 ... or 1a22773-03.

I am on VACA this coming week so I will not be back to
this until after 11/29 ... I will check your response to
this post from home ... Then if I still need to send you
sample of file I will do so when I return.

Happy Thanksgiving Frank ... & Thanks ... Kha

 

[Frank Kabel]

Hi
this should not affect the formula. So just send me an example )