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.

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


"Texas10" wrote in message
...
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.

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 FormatConditional Formatting
3. For Condition1Select 'Formula Is' and enter the above codes whichever
you require.
4. Click Format ButtonFontColor select 'red' then give ok

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

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------

"Texas10" wrote:

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" 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.

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.

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.

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.

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.
--
Alex
*Remember to click "yes" if this post helped you. Thank you!



"Texas10" wrote:

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.

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.

"alexrs2k" wrote:

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.
--
Alex
*Remember to click "yes" if this post helped you. Thank you!



"Texas10" wrote:

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.

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.

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.

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.