Coloring the duplicates with VBA

[Guest]

I would absolutely appreciate any help on the following

How can I color duplicates rows with random colors.
Duplicate are found by searching col A only. e.g a random color (e.g green)
is applied to all occurrence of word "Jane" in col A, then another random
color (e.g) red is applied to all occurrence of word "Jill" and so on.

The idea is visually to see duplicates.
Is this possible ?

Thanks a million for your help

I use XL 2002 on Win 2K

 

[Bob Phillips]

--

HTH

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

 

[Bob Phillips]

Conditional formatting will identify duplicates, but if want random colours
you will need VBA.

--

HTH

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

 

[Guest]

Hi Bob

Yes I was looking for a VBA solution only. Is it possible ?
Thanks

 

[Bob Phillips]

Bobby,

This is what I read that you want, but I have to say, I don't like it

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim rng As Range

Set rng = Range("A1:A100")
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, rng) Is Nothing Then
With Target
For Each cell In rng
If WorksheetFunction.CountIf(rng, cell) > 1 Then
cell.Interior.ColorIndex = Int(Rnd(1) * 56) + 1
End If
Next cell
End With
End If
sub_exit:
Application.EnableEvents = True
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)

 

[Guest]

Thank you so much, I'll give it a try

Hi Bob

Yes I was looking for a VBA solution only. Is it possible ?
Thanks