I’m using the following Code #1 to find duplicates. But I wish to have the original color’s row identified.
I tried using Code #2 but it was designed for another project with someone’s help but it is too ‘Advanced’ for me to modify even after many attempts. Can someone help me with either code.
A copy of the worksheet and it’s code is attached.
Code #1
Code #2
I tried using Code #2 but it was designed for another project with someone’s help but it is too ‘Advanced’ for me to modify even after many attempts. Can someone help me with either code.
A copy of the worksheet and it’s code is attached.
Code #1
Code:
’’’’’’’’’’’’Duplicates
For RowData = RowDataStart To RowDataEnd
Cells(RowData, 19).Clear
Cells(RowData, 19).IndentLevel = 1
If Application.WorksheetFunction.CountIf(Range("G5:G" & RowData), Range("G" & RowData)) > 1 Then
Range("S" & RowData).Value = "Duplicate Color" ‘should show original color row number
Range("S" & RowData).Interior.Color = RGB(255, 255, 0) 'yellow
Range("G" & RowData).Interior.Color = RGB(255, 255, 0) 'yellow
Range("B" & RowData).Interior.Color = RGB(255, 255, 0) 'yellow
ws.Range("A28").Value = "Count of Duplicate Color " & Application.WorksheetFunction.CountA(ws.Range("S5:S" & RowData))
ws.Range("a28").Interior.ColorIndex = 3
Else
Range("S" & RowData).Value = ""
End If
Next RowData
Code #2
Code:
''''''''''''original version on 'test ws' by Marc L @ chandoo forum
' Const D = "Original Color"
' Const E = "Duplicate Color"
' Dim V, R&, L&
' With [D5].CurrentRegion.Columns
' V = Evaluate(Replace("IF(COUNTIF(#,#)>1,#,"""")", "#", .Item(1).Address))
' For R = 5 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) = E & " of Row # " & R + 2
' Next
' V(R, 1) = D & " Row # " & R + 2
' End If
' Next
' .Item(12) = V
' End With