Hello everyone
I have a great code for Mr. Karedog that highlights the duplicate rows with different colors ... It is working great and wonderful
I need to make some little changes ..
As for the attachment for example the whole range is D2:H11 ..
column D is not important for me as it will not be duplicated .. the columns E:H is the important for me
Columns F:H is no problem .. if all found from F:H are the same so it would be treated as duplicates
The problem is for column E .. the duplicates would be for positive and negative values so (1 and -1 are considered duplicates)
Here' the code and I hope it is clear
Sub Karedog()
Dim coll As New Collection, rng As Range, rngDelete As Range, arr, C As Long, I As Long, strKey As String, v1
Set rng = Range("E2").CurrentRegion
rng.Offset(1).Interior.ColorIndex = xlNone
arr = rng.Value
For I = 2 To UBound(arr, 1)
strKey = arr(I, 1) & Chr$(2) & arr(I, 2) & Chr$(2) & arr(I, 3) & Chr$(2) & arr(I, 4)
On Error Resume Next
coll.Add Key:=strKey, Item:=New Collection
On Error GoTo 0
coll(strKey).Add I
Next I
For Each v1 In coll
If v1.Count > 1 Then
C = RGB(Int(Rnd * 256), Int(Rnd * 256), Int(Rnd * 256))
For I = 1 To v1.Count
rng.Rows(v1(I)).Interior.Color = C
If I > 1 Then
If rngDelete Is Nothing Then Set rngDelete = rng.Rows(v1(I)) Else Set rngDelete = Union(rngDelete, rng.Rows(v1(I)))
End If
Next I
End If
Next v1
End Sub
Bookmarks