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