Too many Different Cell Formats Message... Now what?

[Mousehunter]

It all started when I promised a colleague of mine to prepare a script in VBA that would highlight the active cell with the desired colour and then restore the old colour when he would activate another cell. While trying to do so, I found out about the ActiveCell.Interior.Color property which is far superior to the ActiveCell.Interior.ColorIndex I knew about. The former has 256^3 options (almost 16.77mil choices) while the latter only 56.

So VBA must be grouping all different colours of the RGB palette to fit the 56 choices of the ColorIndex. This ought to be around 300 thousand palette choices per ColorIndex. In my effort to store in an Array how many times each ColorIndex is used, I change the Fill Color of the cell A1 looping through all RGB possible permutations, extract the corresponding ColorIndex and keep track of it in an array.

Alas, I never seem to reach the combinations because around the 66th thousand I get the message of the title.

I tried the search but the thread below does not seem to give a clear answer (or it is I who does not understand) as to how am I going to work around this...

RGB colors with Their respective values in one sheet macro

Hi, Good Day, Could anyone write a macro to show the all RGB colours (1.6 million) in one single sheet with a RGB number. I.e. if i place the cursor in a cell then it needs to show/pop-up the RGB value of a particular cell, like wise i need to show the entire 1.6 million RGB colours in one sheet...

www.mrexcel.com

The code I am using can be found below

Sub AllColoursArray()

'Dim AllColoursArray(1 To 256 ^ 3, 1 To 2)
Dim AllStats(1 To 56)
Dim intR As Integer, intG As Integer, intB As Integer, ColourIndex As Integer, ColourCounter As Double

ColourCounter = 0

For intR = 0 To 255
    For intG = 0 To 255
        For intB = 0 To 255
            ColourCounter = ColourCounter + 1
            Cells(1, 1).Interior.Color = RGB(intR, intG, intB)
            ColourIndex = Cells(1, 1).Interior.ColorIndex
'            AllColoursArray(ColourCounter, 1) = ColourIndex
'            AllColoursArray(ColourCounter, 2) = "RGB(" & intR & ", " & intG & ", " & intB & ")"
            AllStats(ColourIndex) = AllStats(ColourIndex) + 1
        Next intB
    Next intG
Next intR

Range("B1:B56").Value = Application.WorksheetFunction.Transpose(AllStats)

End Sub

While I would like to see behind the scenes and understand why I cannot get this to work, the real question is: In the macro I delivered (that highlights a cell and then restores the old colour) If my colleague has a custom colour as a fill color in a cell (one of the 16.77mil), Excel/VBA will classify it in in one of the 56 colour indices. If I only keep track of the colour indices in my code how will it know what colour to restore? Does it not loose the information?

Thanks in advance

PS: I can provide the code I delivered if you wish as well but it ought to be straightforward

 

[Fluff]

Why are you trying to do this?
It would be simpler just to change your existing code to store the color property, rather than the colorindex

 

[rlv01]

It is an excel limitation.

You receive a Too many different cell formats error message in Excel - Microsoft 365 Apps

Discusses that you receive a Too many different cell formats error message in Excel. Provides a resolution.

docs.microsoft.com

You could get around it if you *really want to* by using a succession of new workbooks.

Sub AllColoursArray()
   
    'Dim AllColoursArray(1 To 256 ^ 3, 1 To 2)
    Dim AllStats(1 To 56)
    Dim intR As Integer, intG As Integer, intB As Integer, ColourIndex As Integer, ColourCounter As Long
    Dim WB As Workbook, WS As Worksheet

    Set WB = Application.Workbooks.Add
    Set WS = WB.Worksheets(1)
       
    ColourCounter = 0
    Application.ScreenUpdating = False
    For intR = 0 To 255
        For intG = 0 To 255
            For intB = 0 To 255
                If ColourCounter <= 60000 Then
                    ColourCounter = ColourCounter + 1
                    With WS
                        .Cells(1, 1).Interior.Color = RGB(intR, intG, intB)
                        ColourIndex = .Cells(1, 1).Interior.ColorIndex
                    End With
                   
                    '            AllColoursArray(ColourCounter, 1) = ColourIndex
                    '            AllColoursArray(ColourCounter, 2) = "RGB(" & intR & ", " & intG & ", " & intB & ")"
                    AllStats(ColourIndex) = AllStats(ColourIndex) + 1
                Else
                    DoEvents
                    WB.Close SaveChanges:=False
                    Set WB = Application.Workbooks.Add
                    Set WS = WB.Worksheets(1)
                    ThisWorkbook.Activate
                    ColourCounter = 0
                    DoEvents
                End If
            Next intB
        Next intG
    Next intR
    WB.Close SaveChanges:=False
    Application.ScreenUpdating = True
    Range("B1:B56").Value = Application.WorksheetFunction.Transpose(AllStats)
End Sub

But Fluff asks the important question, which is "why?" If all you are trying to do for your colleague is highlight the active cell with the desired color and then restore the old color when another cell is activated, then just store the color property and forget about colorindex.

 

[Fluff]

Should be able to do it with conditional formatting as well, but will take a fair amount of time run.

 

[Fluff]

For what it's worth, this is a list of the colour index numbers & a count of how many times they occur when compared to the colour property

+Fluff 1.xlsm
	A	B	C
1	Colour index	count	Colour
2	1	48,724
3	2	27,033
4	3	209,183
5	4	319,215
6	5	178,152
7	32	0
8	6	129,129
9	27	0
10	7	414,661
11	26	0
12	8	88,530
13	28	0
14	9	149,378
15	30	0
16	10	248,508
17	11	110,716
18	25	0
19	12	535,154
20	13	492,784
21	29	0
22	14	309,300
23	31	0
24	15	606,944
25	16	579,510
26	17	423,806
27	18	1,145,568
28	54	0
29	19	56,857
30	20	44,902
31	34	0
32	21	184,313
33	22	649,971
34	23	309,374
35	24	112,703
36	33	108,579
37	35	269,897
38	36	126,802
39	37	250,065
40	38	339,077
41	39	285,324
42	40	292,667
43	41	436,877
44	42	1,020,541
45	43	885,226
46	44	200,047
47	45	227,586
48	46	290,107
49	47	743,915
50	48	743,364
51	49	197,753
52	50	1,177,974
53	51	65,664
54	52	172,796
55	53	360,798
56	55	630,420
57	56	577,322
List

 

[Mousehunter]

Why are you trying to do this?
It would be simpler just to change your existing code to store the color property, rather than the colorindex

Thank you for your time and I am sorry for the late response.

This very same problem had been tackled by another experienced member in the past (without the use of Conditional Formatting. Surprisingly while his code was doing the job, "it did not exactly clean up the mess" as it was doing something with the grids of the deactivated cell. I am scanning through the site in order to find the snippet. He was using the ActiveCell.Interior.Color.

I was such in a hurry to deliver that I did not drill down to the very bottom of why this was happening. I just modified his code to use the ActiveCell.Interior.ColorIndex but I was really concerned that 56 choices could not be holding 16.77mil choices...

I 'll be back in a while

 

[Mousehunter]

Why are you trying to do this?
It would be simpler just to change your existing code to store the color property, rather than the colorindex

This is the post I referred to. The solution is not restoring the cell as it exactly was

Change colour of cell while active

In VBA, I would like to change the colour of the active cell, and reset it to its original colour when exited. Can you help? Thanks!

www.mrexcel.com

Option Explicit
Dim LastCellColor As Long, LastCellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Len(LastCellAddress) Then Range(LastCellAddress).Interior.Color = LastCellColor
  LastCellAddress = ActiveCell.Address
  LastCellColor = ActiveCell.Interior.Color
  ActiveCell.Interior.Color = vbRed
End Sub

While if you modify it dropping the Color and using the Colorindex...

Option Explicit

Dim LastCellColor As Long, LastCellAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Len(LastCellAddress) Then Range(LastCellAddress).Interior.ColorIndex = LastCellColor
LastCellAddress = ActiveCell.Address
LastCellColor = ActiveCell.Interior.ColorIndex
ActiveCell.Interior.ColorIndex = 6
End Sub

 

[Fluff]

Why did you change color to colorindex? As I said in post#2 just use Color & forget about colorindex.

 

[Mousehunter]

Why did you change color to colorindex? As I said in post#2 just use Color & forget about colorindex.

Did you test Mr Rothstein's code? Does it work properly? Because this is what I get using the ActiveCell.Interior.Color

Can you please test the code? Because if only I get this, perhaps there is something wrong with my settings. Or something else...

 

[Fluff]

You have no coloured cells on that sheet, so I don't understand what you are trying to do.