Highlight duplicate value in a column

[lucpian]

Hi All,

I wrote the following VBA code to check for duplicates in a column in my worksheet, but it is not working. Please, I would be grateful if someone in the forum will help me out.

[VBA]Function CheckforDuplicateBarcodes(columnname As Integer)
Dim rowcount
Dim R
rowcount = Range("A65536").End(xlUp).Row
For R = 2 To rowcount
myCheck = ActiveCell
If ActiveCell = myCheck Then
Sheet1.Cells(R, columnname).Interior.ColorIndex = 5
End If

Next
End Function

Sub Duplibutton()
CheckforDuplicateBarcodes (14)
End Sub[/VBA]

Thanks

Lucpian

[Zack Barresse]

Hi there Lucpian,

A quick search of the KB retreived these results...

http://vbaexpress.com/kb/getarticle.php?kb_id=985
http://vbaexpress.com/kb/getarticle.php?kb_id=476
http://vbaexpress.com/kb/getarticle.php?kb_id=135
http://vbaexpress.com/kb/getarticle.php?kb_id=8

HTH

[lucpian]

Hi there Lucpian,

A quick search of the KB retreived these results...

http://vbaexpress.com/kb/getarticle.php?kb_id=985
http://vbaexpress.com/kb/getarticle.php?kb_id=476
http://vbaexpress.com/kb/getarticle.php?kb_id=135
http://vbaexpress.com/kb/getarticle.php?kb_id=8

HTH

[Zack Barresse]

Also take a look at the Duplicate Master add-in by brettdj, does what you are asking very, very well....


http://members.iinet.net.au/~brettdj/

[Bob Phillips]

Why not just use conditional formatting?

[lucpian]

Thanks all, but the problem is that the function call will be part of a menu that when the user clicks will basically highlight the duplicate values in the column name. The function should be reusable so that all I need do is call it using the integer equivalent of the column name. My basic problem is a code that will test if the values are the same or exist in that range and if does it highlights the cell. Right now it is highlighting the entire column being reference in the calling function.

Thanks, again

Lucpian

[Bob Phillips]

[vba]

Function CheckforDuplicateBarcodes(columnname As Integer)
Dim rowcount
Dim R
With Sheet1
rowcount = .Cells(.Rows.Count, columnname).End(xlUp).Row
For R = 2 To rowcount
If Application.CountIf(.Columns(columnname), .Cells(R, columnname).Value) > 1 Then
.Cells(R, columnname).Interior.ColorIndex = 5
End If
Next R
End With
End Function

Sub Duplibutton()
CheckforDuplicateBarcodes 14
End Sub
[/vba]

[lucpian]

xld, you are awesome. You are a vba guru. Thanks, your code works perfectly.

-Lucpian