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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.