Duplication and colors

  • Thread starter Thread starter Stevie
  • Start date Start date
S

Stevie

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


--

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

FormatConditions.Add Type:=xlExpression

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

Thanks

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


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
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
 
Thanks Bob - you are a real star.

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

Thanks again.
 
Back
Top