Color duplicates

[saman110 via OfficeKB.com]

Hello,

I want Excel to color duplicates in different color and each set of
duplicates have same color. I found this macro, but it does not highlight the
set of duplicates in same color.

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



Any Help?

Thx.

 

[Gord Dibben]

Change the one line

cell.Interior.ColorIndex = Int(Rnd(1) * 56) + 1\

to this for red

cell.Interior.ColorIndex = 3


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Ignore this, it will change all cells to red. Not what you wanted.


Gord