Color Coding Stops After I get to # 65,282

[Daxton A.]

I'm just having fun. What I am doing is trying to get all 16,777,216 colors (256 x 256 x 256)
to show up, but it stops posting my colors after it formats cell # 65,282...gives me an error
stating that there are too many formats. Anyone know a work around because I can highlight
all 65,282 cells and copy and paste them and it does not seem to have a problem.

(Excel 2010) :thumb
P.S. My RAM only allows me to have a row count of 1,048,568 cells, I understand I would
have to restart my loops several times to get all 16,777,216 colors but that's later down the
road.




Dim i As Double 'I used the Double format bc it was large enough.
Dim j As Double '(This was originally formatted Long)
Dim k As Double
Dim l As Double

'**************************************************************************
'Name: Daxton Allen
'Purpose: This Macro automatically shows you all of the colors.
'Date: 02-15-1018
''************************************************************************* *

Application.DisplayAlerts = False
Application.ScreenUpdating = False

i = 0
j = 0
k = 0
l = 1

'.Color(0 - 255,0,0) = .ColorIndex 1 - 256
'.Color(0 - 255,0 - 255,0) = .ColorIndex 257 - 65,536
'.Color(0 - 255,0 - 255,0 - 255) = .ColorIndex 65,537 - 16,777,216


For i = 1 To 255
Range("D" & (l + 9)).Interior.Color = RGB(i - 1, 0, 0)
Range("E" & (l + 9)) = "(" & l - 1 & ", 0 , 0)"
l = l + 1

For j = 1 To 255
Range("D" & (l + 9)).Interior.Color = RGB(i - 1, j - 1, 0)
Range("E" & (l + 9)) = "(" & l - 1 & ", 0 , 0)"
l = l + 1

For k = 1 To 255
Range("D" & (l + 9)).Interior.Color = RGB(i - 1, j - 1, k - 1)
Range("E" & (l + 9)) = "(" & l - 1 & ", 0 , 0)"
l = l + 1

Next k
Next j
Next I

[Leith Ross]

Hello Daxton A.,

When you change a single cell's color it counts as a single format, but if you change a range of cells to the same color, it counts as single format.

Another problem you will have is displaying your colors. The worksheets cells are limited to 56 colors only. If you change the color of a cell to a color not in the palette, it will choose the closest available color. To display all the colors you will need change the background color of a Shape or ActiveX control on the worksheet. Fonts will also display the full range of colors.

[p45cal]

The worksheets cells are limited to 56 colors only. If you change the color of a cell to a color not in the palette, it will choose the closest available color. I don't think that's been the case since around Excel 2003.

If you alter your code to:

For ?? = 1 To 255 Step 7

in the 3 places you have such lines, you should get through the whole range (I doubt you'll be able to tell the difference between two adjacent colours).
Best to start with a virgin workbook each time.

This snippet does something similar:

Sub blah()
Stepsize = 7
Application.ScreenUpdating = False
l = 10
For r = 1 To 255 Step Stepsize
For g = 1 To 255 Step Stepsize
For b = 1 To 255 Step Stepsize
Range("D" & l).Interior.Color = RGB(r, g, b)
Range("E" & l) = "'(" & r & "," & g & "," & b & ")"
l = l + 1
Next b
Next g
Next r
Application.ScreenUpdating = True
End Sub