• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Duplicate's Cell Address

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
According to your attachment a starter VBA demonstration :​
Code:
Sub Demo1()
      Const D = " Duplicate"
        Dim V, R&, L&
    With [G5].CurrentRegion.Columns
        V = Evaluate(Replace("IF(COUNTIF(#,#)>1,#,"""")", "#", .Item(1).Address))
    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 !​
 
Top