Highlight duplicates
 

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.

Highlight duplicates
 

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

-------------------


Sincerely,
Leith Ros

--
Leith Ros
-----------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...fo&userid=1846
View this thread: http://www.excelforum.com/showthread.php?threadid=55716

Highlight duplicates
 

Hi SITCFanTN,

This link has some useful information
http://www.cpearson.com/excel/duplicat.htm.
cheers
Odin
SITCFanTN wrote:
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.

Highlight duplicates
 

This worked great, thanks a bunch Leith!

"Leith Ross" wrote:


Hello SITCFanTN,

Add a VBA module to your Workbook and paste the macro code below into
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

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=557160

Highlight duplicates
 

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

"Leith Ross" wrote:


Hello SITCFanTN,

Add a VBA module to your Workbook and paste the macro code below into
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

--------------------


Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=557160