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

Mousehunter

New Member
Joined
May 6, 2008
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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...


The code I am using can be found below
VBA Code:
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

New Member
Joined
May 6, 2008
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
It is an excel limitation.


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

VBA Code:
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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Mousehunter

New Member
Joined
May 6, 2008
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
1628707276277.png


.Colorindex below
1628707335553.png


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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,048
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

Well-known Member
Joined
May 16, 2017
Messages
1,160
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
You may just need to handle the special case of ColorIndex = -4142

VBA Code:
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

MrExcel MVP
Joined
Aug 18, 2015
Messages
11,686

ADVERTISEMENT

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

VBA Code:
ActiveWorkbook.Colors(10) = RGB(128, 128, 128)
 

Mousehunter

New Member
Joined
May 6, 2008
Messages
25
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
@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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,048
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,342
Messages
5,746,192
Members
423,997
Latest member
eakenila

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top