Check for duplicate entries

  • Thread starter Thread starter cstang
  • Start date Start date
C

cstang

Hello,

Can anyone pls help me to solve the below
question?

I've 3 columns typed with IC No (Column C,E & G),
I want to check whether the IC No are duplicate in
these columns. If there is duplicate then highlight the
cell with interior.indexcolor, next duplicate with
difference color....

Thanks in advance.

Cstang
 
Greg,

While some colors may be repeated, dpending on column, this quick and dirty solution below should help.

HTH,
Bernie
Excel MVP

Sub HighLightDuplicates()

Dim mycell As Range
Dim myRange As Range
Dim myRange2 As Range
Dim myCount As Integer

Set myRange = Intersect(ActiveSheet.UsedRange, Range("C:C,E:E,G:G"))
For Each mycell In myRange
If mycell.Value <> "" Then
myCount = Application.CountIf(Range(myRange(1), mycell), mycell.Value)
If myCount > 1 Then
mycell.Interior.ColorIndex = myCount + 1
End If
End If
Next mycell
End Sub
 
Back
Top