Conditional format help

[Texas10]

How can I have a cell be highlighted at the beginning of a certain event?
Here's my example, I have 2 columns and in the right column it's a bunch of
numbers. The numbers are all different but there comes a point when they
start to repeat and I want excel to recognize the repeating cells and
highlight the cell to the left of the start of the repetition. Here's an
example because it's hard to explain, 2 columns and I'll just call one
letters and the other is numbers:
A 2
B 5
C 3
D 8
E 7
F 7
G 7
H 7

So I want it to highlight the cell called E here since it's the start of the
repetition. Any help would be greatly appreciated.

 

[T. Valko]

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 Format>Conditional 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

 

[Ms-Exl-Learner]

For Finding the All Duplicate Values (Original & Duplicate Values)
=COUNTIF($B:$B,$B1)>1

For Finding the First Original Value:-
=IF(COUNTIF($B:$B,$B1)>1,COUNTIF($B$1:$B1,$B1)=1)

For finding the Duplicate Values Apart from Original Values:-
=IF(COUNTIF($B:$B,$B6)>1,COUNTIF($B$1:$B6,$B6)>1)

1. Place the cursor in A1 and select the Entire Column (i.e.) A Column (if
you want to highlight only the A Column then Place the cursor in A1 Cell and
Select A Column, Otherwise select the Multiple columns, but the when
applying the conditional formatting the (active cell) cursor should be in 1st
cell of any Column)

2. From menu Format>Conditional Formatting>
3. For Condition1>Select 'Formula Is' and enter the above codes whichever
you require.
4. Click Format Button>Font>Color select 'red' then give ok

You can change the Formula refence B column to your desired Column.

If this post helps, Click Yes!

 

[Texas10]

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 Format>Conditional 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

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.

 

[T. Valko]

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

 

[T. Valko]

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!

 

[Texas10]

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.

 

[alexrs2k]

Hi.
You could do this with a macro. All you need to do is open VB Editor
(Alt+F11). In Thisworbook copy this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim Lrow As Long
With ActiveSheet
Lrow = .Range("A" & Rows.Count).End(xlUp).Row
i = Lrow
Do While True
If .Range("A" & i).Value <> .Range("A" & i - 1).Value Then
Exit Do
Else
i = i - 1
End If
Loop
.Range("A" & i & ":A" & Lrow).Interior.ColorIndex = 8 'Cyan
End With
End Sub

Make sure your data is on column "A". If you need several columns the code
might slightly change. You could also change the default column "A" to
whatever column you wish.

 

[Texas10]

Thanks Alexrs2k but the only problem is that I need it to be simple as
possible and probably using condition format since there are others that will
be using and changing it later if need be. I'm afraid to do a macro since
they will all be lost, lol.

 

[T. Valko]

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)))))

 

[Texas10]

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]

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!