Duplication and colors

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Stevie - 18 Jun 2005 18:12 GMT

Hi

Can anyone please help me with the following :-

I have 5 columns and about 60 rows of various numbers. There are a
number of duplicate entries in some of the cells. When I have the
cursor on a certain cell, say B5, is there a way for excel to find and
then hilight all the enteries with the same value ?
When I then cursor down, I want to be able to clear the current
selection and then check for the same duplication etc etc.

Any help would be appreciated.

Thanks.

Reply to this Message

Bob Phillips - 18 Jun 2005 19:30 GMT

Here is one way

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const myRange As String = "A1:E60"
If Target.Count = 1 Then
If Not Intersect(Target, Range(myRange)) Is Nothing Then
With Range(myRange)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & Target.Address & "=" &
Target.Address(False, False)
.FormatConditions(1).Interior.ColorIndex = 3
End With
End If
End If
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> Hi
>
[quoted text clipped - 10 lines]
>
> Thanks.

Reply to this Message

STEVIE - 18 Jun 2005 22:22 GMT

Thanks for your reply Bob. unfotuntatel, I get a syntax error
with the line

FormatConditions.Add Type:=xlExpression

Formula1:="=" & Target.Address & "=" &
Any ideas ??

Thanks

Stevie.

>Here is one way
>
[quoted text clipped - 17 lines]
>'code module. To do this, right-click on the sheet tab, select
>'the View Code option from the menu, and paste the code in.

Reply to this Message

Norman Jones - 18 Jun 2005 22:38 GMT

Hi Steve,

Bob's code has benn assaulted by the line wrap demon. Try this attempt at
thwarting the demon's attentions:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const myRange As String = "A1:E60"
If Target.count = 1 Then
If Not Intersect(Target, Range(myRange)) Is Nothing Then
With Range(myRange)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & Target.Address & "=" & _
Target.Address(False,
False)
.FormatConditions(1).Interior.ColorIndex = 3
End With
End If
End If
End Sub

---
Regards,
Norman

> Thanks for your reply Bob. unfotuntatel, I get a syntax error
> with the line
[quoted text clipped - 30 lines]
>>'code module. To do this, right-click on the sheet tab, select
>>'the View Code option from the menu, and paste the code in.

Reply to this Message

Bob Phillips - 18 Jun 2005 23:25 GMT

LOL. So did your attempt Norman. It's a real PITA.

Regards

Bob

> Hi Steve,
>
[quoted text clipped - 55 lines]
> >>'code module. To do this, right-click on the sheet tab, select
> >>'the View Code option from the menu, and paste the code in.

Reply to this Message

Norman Jones - 18 Jun 2005 23:36 GMT

Hi Bob,

And that was with me TRYING!

---
Regards,
Norman

> LOL. So did your attempt Norman. It's a real PITA.
>
[quoted text clipped - 62 lines]
>> >>'code module. To do this, right-click on the sheet tab, select
>> >>'the View Code option from the menu, and paste the code in.

Reply to this Message

Bob Phillips - 18 Jun 2005 22:38 GMT

One day I will crack wrap-around! Try this version

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const myRange As String = "A1:E60"
If Target.Count = 1 Then
If Not Intersect(Target, Range(myRange)) Is Nothing Then
With Range(myRange)
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & Target.Address & "=" &
_
Target.Address(False, False)
.FormatConditions(1).Interior.ColorIndex = 3
End With
End If
End If
End Sub

Signature

HTH

RP
(remove nothere from the email address if mailing direct)

> Thanks for your reply Bob. unfotuntatel, I get a syntax error
> with the line
[quoted text clipped - 29 lines]
> >'code module. To do this, right-click on the sheet tab, select
> >'the View Code option from the menu, and paste the code in.

Reply to this Message

STEVIE - 18 Jun 2005 23:06 GMT

Thanks Bob - you are a real star.

I really appreciate your kind help with this. You are a genius.

Thanks again.

>One day I will crack wrap-around! Try this version
>
[quoted text clipped - 13 lines]
> End If
>End Sub

Reply to this Message