Bypassing colour limitations of Interior.ColorIndex?

[jack2]

At the moment, Interior.ColorIndex (if I am correct) has a limitation of 56 colours?

Is there any way I can get around that? As can be seen, after row 55, it doesn't highlight duplicates (The code highlights duplicates if that record has a duplicate)

File: https://www.dropbox.com/s/2k7eqr85oienk2b/colour-cells.xls?dl=0

Code:

Sub different_colour()Dim lrow As Integer
lrow = Worksheets("sheet1").Range("B2").CurrentRegion.Rows.Count - 1 + 2
For N = 3 To lrow
If Application.WorksheetFunction.CountIf(Worksheets("sheet1").Range("B3:B" & lrow), Worksheets("sheet1").Range("B" & N)) = 1 Then
GoTo skip
Else
Worksheets("sheet1").Range("B" & N).Interior.ColorIndex = Application.WorksheetFunction.Match(Worksheets("sheet1").Range("B" & N), Worksheets("sheet1").Range("B3:B" & lrow), 0) + 2
End If
skip: Next N
Worksheets("sheet1").Activate
Range("B3").Select
End Sub

 

[Peter_SSs]

Try using the color property instead. For example, something like

Worksheets("sheet1").Range("B" & N).Interior.Color = 1000 + 10000 * Application.WorksheetFunction.Match(Worksheets("sheet1").Range("B" & N), Worksheets("sheet1").Range("B3:B" & lrow), 0)