Bypassing colour limitations of Interior.ColorIndex?

jack2

New Member
Joined
Jul 6, 2018
Messages
8
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

 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,287
Office Version
  1. 365
Platform
  1. Windows
Try using the color property instead. For example, something like
Code:
Worksheets("sheet1").Range("B" & N).Interior.Color = 1000 + 10000 * Application.WorksheetFunction.Match(Worksheets("sheet1").Range("B" & N), Worksheets("sheet1").Range("B3:B" & lrow), 0)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,341
Messages
5,528,146
Members
409,802
Latest member
joeino

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top