#### kds14589

##### New Member
I have the following code to find and identify Duplicates.
Code:
``````   Dim last_Row_d As Long
last_Row_d = ws.Range("G" & Rows.count).End(xlUp).Row
Dim x As Long
For x = 5 To last_Row_d
If Application.WorksheetFunction.CountIf(ws.Range("G5:G1000"), ws.Range("G" & x)) > 1 Then
ws.Range("I" & x).Value = "Duplicate "
ws.Range("I" & x).Interior.Color = RGB(255, 255, 0)      'yellow
ws.Range("G" & x).Interior.Color = RGB(255, 255, 0)     'yellow
Else
ws.Range("I" & x).Value = ""
End If
Next x``````
is it possible to id. the other duplicate cell's address? Such as "Duplicate of cell \$G\$63" instead of just "Duplicate"

#### Marc L

##### Excel Ninja
As you forgot to attach the workbook try the Range.Find method for example …​

#### kds14589

##### New Member
sorry this post was a 'last minute' and I didn't realize the sample workbook didn't attach.

#### Attachments

• 17.4 KB Views: 9
Last edited by a moderator:

#### Marc L

##### Excel Ninja
According to your attachment a starter VBA demonstration :​
Code:
``````Sub Demo1()
Const D = " Duplicate"
Dim V, R&, L&
With [G5].CurrentRegion.Columns
For R = 1 To .Rows.Count - 1
If V(R, 1) Like "R*" Then
For L = R + 1 To .Rows.Count
If V(L, 1) = V(R, 1) Then V(L, 1) = " G" & R + 4 & D
Next
V(R, 1) = D
End If
Next
.Item(3) = V
End With
End Sub``````
Do you like it ? So thanks to click on bottom right Like !​

#### kds14589

##### New Member
Just what I needed.
THANKS

Last edited by a moderator: