Check for duplicate entries

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
 
B

Bernie Deitrick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Remove Duplicates 5
Duplicate value with blank space 17
Pls help me in finding duplicate entries in a column range 2
Check for Duplicates then Sum cells of duplicates 7
Delete Rows with Conditions 1
Finding and highlighting duplicates across multiple worksheets 4
Deleting Duplicate with a Msg box which displays no of duplicates 7
Check for duplicate values 5

Top