Well, to be honest I just "spit 'em out" *but* it takes some time to test
'em and make sure they work! That's why it's important to know all the
details.

Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Texas10" wrote in message
...
Nice, thanks Valko...you're awesome. Tell me you do some tinkering to
figure
that out because if you're just spitting these formulas out then that's
insane!

"T. Valko" wrote:

Ok, this is quit complex!!!

This works under the following conditions:

The data in column A is unique (no duplicates)
The sequence of repeating numbers at the end of data in column B will
always
be the longest sequence of repeats. There can be other sequences of the
repeating number that are the same size but there can't be any sequences
of
the repeating number that are longer.

For example:

A...1
B...1
C...1
D...0
E...0
F...1
G...1
H...1

F will be the highlighted value.

Same setup for the conditional formatting but use this formula:

=A2=INDEX(A$2:A$9,COUNT(B$2:B$9)-MAX(FREQUENCY(IF(B$2:B$9=LOOKUP(1E100,B$2:B$9),ROW (B$2:B$9)),IF(B$2:B$9<LOOKUP(1E100,B$2:B$9),ROW(B $2:B$9)))))

--
Biff
Microsoft Excel MVP


"Texas10" wrote in message
...
Okay sorry let me add some things i should've said, the repeating
number
will
end the list but it is possible to have another string of repetitions
before
this one. Can you have it where it will recognize a repetition of
about 3
or
more and highlight that cell next to it only when the repetition of 3
or
more
occurs??? So I could have 2 highlighted cells in column A if B has more
than
1 string of repetitions. Column B will always end with a repitition
though.

"T. Valko" wrote:

Also, does the repeating number always end the list? Like this:

3
2
3
3
3

Or, is this possible:

3
2
3
3
1

We're gong to need a thorough explanation of the possibilities!

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Well, you said the numbers are all different until they start
repeating
so
I assumed every number is unique except for the number that repeats
and
the repeating number only shows up where it starts to repeat.

Is it possible that there is more than one repeating number:

3
2
3
3
2
2


--
Biff
Microsoft Excel MVP


"Texas10" wrote in message
...


"T. Valko" wrote:

The numbers are all different but there comes a
point when they start to repeat

Try this...

With your data in the range A2:B9...

Selet the *entire* range A2:A9 starting from cell A2. Cell A2 will
be
the
active cell. The active cell is the one cell in the selected range
that
is
not shaded. The formula will be relative to the active cell.

Goto the menu FormatConditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:
=AND(COUNTIF(B$2:B$9,B2)1,COUNTIF(B$2:B2,B2)=1)
Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP

Okay this formula works but it highlights the first value that's
part
of
the
repetition. So just in case I have a 5 in B2 but then the
repetition
of
5's
starts in B10 it will highlight B2 since there was a 5 there. Can
you
have
it only do the highlight when it's the beginning of the repetition
part??
This could work but it's possible that I might have avalue earlier
in
the
column that matches the repetition value.
Ms-learner the formulas you posted didn't do what I need, thanks
but
they
all highlight different areas.