Highlight rows with different colors by groups of duplicates

Solution 1:

The solution by Gowtham is only specific to numbers and uses VBA. You can use the following workaround that works with any type of data and doesn't need VBA.

We could use another column that generates a unique value for all the duplicates using a formula and use the "Conditional Formatting > Color Scales" for that column. Screenshot below.

The formula that you can use is

"=ROW(INDEX(A$2:A$12,MATCH(A2,A$2:A$12,0)))"

In the above formula, A$2:A$12 is the range that we want to search for duplicates.

The formula basically searches for the first instance of the duplicate value in the given range and inputs the row number of that first instance.

P.S: In the above formula, the range "A$2:A$12" is a fixed range, using the above formula in a Table is much simpler as a Table Range is dynamic

One other benefit of using Table is that we can even sort the data to group the duplicate values together

=ROW(INDEX([Column1],MATCH(A2,[Column1],0)))

Solution 2:

Try out this simple code and modify it per your needs. Its quite self explanatory,

Sub dupColors()
Dim i As Long, cIndex As Long
cIndex = 3
Cells(1, 1).Interior.ColorIndex = cIndex
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(i, 1) = Cells(i + 1, 1) Then
        Cells(i + 1, 1).Interior.ColorIndex = cIndex
    Else
        If Cells(i + 1, 1) <> "" Then
            cIndex = cIndex + 1
            Cells(i + 1, 1).Interior.ColorIndex = cIndex
        End If
    End If
Next i
End Sub