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

 

[Mousehunter]

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.

Thank you very much, very useful.

 

[Mousehunter]

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

Perhaps I have not communicated well what this is all about. Forgive me English is not my first language.

This has nothing to do with coloured cells. It has to do with:

1. highlighting the active cell with a colour of the user's choice and
2. restoring the cell as it was regardless of it had a fill colour or not.

Not EVERY cell will be coloured in the sheet. I am a Financial Analyst and below is one of the spreadsheets I use for demonstration of the effect of the two snippets

The difference of the two snippets is that the .Color messes with the gridlines while the .Colorindex does not.

.Color below

.Colorindex below

Do we have an idea as to why this is happening? Does .Color modify something else that we should be restoring on our way out? I understand that your suggestion is far superior to mine because it has 16.77mil choices while mine has only 56.

I also understand that if I am to deliver something I must be able to clean up the mess I am causing the user. I must only highlight, not change their borders and mess with their styling.

Thank you for the time you have invested

 

[Fluff]

Ok, how about

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

 

[rlv01]

You may just need to handle the special case of ColorIndex = -4142

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Trim(LastCellAddress) = "" Then
        Me.Range(LastCellAddress).Interior.Color = LastCellColor
    End If
    
    With ActiveCell
        If .Interior.ColorIndex = -4142 Then
            LastCellColor = xlNone
        Else
            LastCellColor = .Interior.Color
        End If
        
        LastCellAddress = .Address
        .Interior.Color = vbRed
    End With
End Sub

 

[Eric W]

It might be worth mentioning that Excel has 56 default values for the ColorIndex, but if you want to change them, you can:

ActiveWorkbook.Colors(10) = RGB(128, 128, 128)

 

[Mousehunter]

@rlv01 , @Fluff

I cannot mark both of your codes as a solution because the solution must be unique (I tried it). But both codes qualify as such. Thank you very much for your time.

PS: Of course I did not understand why the .Colorindex does not need special treatment of a non-coloured cell while the .Color does, nor did I understand how by storing in the "LastCellColour" variable the .Colorindex number with only 56 options (and not the .Color with 16.77mil options), Excel VBA seems to recognise the very same RGB permutation and be able to restore it afterwards, but sometimes there is no point in doing so since something does the job.

Thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.