Subscript Out of Range Error Identifying Duplicates Using Color Scale

[ChrisOK]

This code seems to work great in identifying duplicates within a column; uses colors to group 'like' dups - but is throwing the Subscript Out of Range Error 1/2 way through it's identification process (when it's running on a longer list) -- short lists - it doesn't throw the error.
I found it for use and am not the original author of it -- so I'm not altogether sure about it's architecture and why it occurs but have a guess!

Researching other posts w/ this error - indicated a range wasn't correctly identified or a name of a worksheet was not found -- but don't think those apply in my case... I don't think this code refers to any specific workbook or worksheet name... nor is the range I'm using wrong.. A2:A65536 should be good...

I thought maybe it has to do with the color scale range? Color Palette and the 56 Excel ColorIndex Colors
I think on long lists, it goes down that color scale and uses the +1 (next avail color) but once it gets down to the bottom #56 it freaks out!
I'm not altogether fond of it using diff colors and would assume it just color all dups one color but sadly, this code wasn't written to do that..

Hoping to get some help on:
1---how to correct this code to where it won't trigger once it hits #56 and instead keeps going somehow..
2---how to perhaps change this code to where it simply uses one color to highlight all dups

This is the line that's highlighted when the error triggers:
cel.Interior.ColorIndex = clr

Sub Find_Duplicate_Entry()
'THIS CODE FINDS DUPLICATES WITHIN A COLUMN AND HIGHLIGHTS THEM BY COLOR
'FOR EXAMPLE: "DOG" MAY BE FOUND 3 TIMES AND ALL 3 WOULD BE COLORIZED YELLOW
'THEN "CAT" MIGHT BE FOUND 2 TIMES AND BOTH COLORIZED GREEN
'THEN "BIRD 16 TIMES, ALL 16 BIRD CELLS WOULD BE BLUE

    Dim cel As Variant
    Dim myrng As Range
    Dim clr As Long
    Set myrng = Range("A2:A" & Range("A65536").End(xlUp).Row)
    myrng.Interior.ColorIndex = xlNone
    clr = 35
    For Each cel In myrng
        If Application.WorksheetFunction.CountIf(myrng, cel) > 1 Then
            If WorksheetFunction.CountIf(Range("A2:A" & cel.Row), cel) = 1 Then
                cel.Interior.ColorIndex = clr
                clr = clr + 1
            Else
                cel.Interior.ColorIndex = myrng.Cells(WorksheetFunction.Match(cel.Value, myrng, False), 1).Interior.ColorIndex
            End If
        End If
    Next
End Sub

 

[pgc01]

Hi

2---how to perhaps change this code to where it simply uses one color to highlight all dups

Delete the statement

                clr = clr + 1

This way clr won't change value.

 

[ChrisOK]

Thanks PGC! It's crazy how just a tiny edit can make such a big difference!
Your solution to #2 works perfectly to do all dups in one color!

 

[pgc01]

... and if you want to stop at 56 use

If clr < 56 Then clr = clr + 1