PDA

View Full Version : Highlight duplicate value in a column



lucpian
03-11-2008, 09:22 AM
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.

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

Thanks

Lucpian

Zack Barresse
03-11-2008, 09:28 AM
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
03-11-2008, 12:06 PM
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
03-11-2008, 12:38 PM
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
03-11-2008, 12:40 PM
Why not just use conditional formatting?

lucpian
03-11-2008, 01:03 PM
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:giggle

Bob Phillips
03-11-2008, 01:56 PM
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

lucpian
03-11-2008, 02:07 PM
xld, you are awesome. You are a vba guru. Thanks, your code works perfectly.

-Lucpian