Duplication and colors

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

Bob Phillips

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

STEVIE

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

Bob Phillips

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

Norman Jones

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
 
S

STEVIE

Thanks Bob - you are a real star.

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

Thanks again.
 
Top