Highlight duplicates

[Guest]

I've searched this site and see all kinds of posts to delete duplicates, but
nothing about highlighting them. I have a sheet with 1000 of rows and would
like VBA code to look in Column B titled "GID" and highlight or somehow spike
out duplicate numerical values. Any help you can give me is appreciated,
thank you.

 

[Leith Ross]

Hello SITCFanTN,

Add a VBA module to your Workbook and paste the macro code below int
it.


Code
-------------------

Sub HighlightDuplicates()

'Highlight duplicates in Yellow
Dim Cell As Range
Dim Cell_Range As Range
Dim MyCollection As New Collection

'Find last cell entry in the row
LastEntry = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Address
'Define the range to examine
Set Cell_Range = ActiveSheet.Range("B1", LastEntry)

For Each Cell In Cell_Range
On Error Resume Next
MyCollection.Add Item:="1", Key:=Cell.Text
If Err.Number = 457 Then
Cell.Interior.ColorIndex = 6
Err.Clear
End If
Next Cell

End Sub

 

[Odin]

Hi SITCFanTN,

This link has some useful information
http://www.cpearson.com/excel/duplicat.htm.
cheers

 

[Guest]

This worked great, thanks a bunch Leith!

 

[Dylan]

Is there any way to check and highlight the entire row rather than a column

I want to find out if any entries in my expense databse have been entered
twice, because the totals do not add up.

Dylan