custom cell color

[mark kubicki]

is there a way to add a "custom color" to a cell format

range().Interior.ColorIndex = (1 thru 56)

I only seem to be able to use the 56 predefined colors?

thanks in advance,
mark

 

[Bob Phillips]

You can only use a colour from the colour palette, but you can change the
colour in there. Tools>Options>Color>Modify.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[John Coleman]

You can change the palette. Say you think that you can live without
colorindex 37 but you want color RGB(123,57,39). Then, just execute

Sub Change()
ActiveWorkbook.Colors(37) = RGB(123,57,39)
End Sub


And - colorindex 37 has been redefined. I think that there is a chart
showing colorindices and corresponding color somewhere in help.

If not - execute

Sub showcolors()
Dim i
For i = 1 to 56
Cells(i,1).Value = i
Cells(i,2).Interior.Coloridex = i
Next i
End Sub

To see which of those 56 colors you wouldn't pine for.

HTH

-John Coleman

 

[John Coleman]

My code had a typo - It should have had ".Colorindex" rather than
".Coloridex" in the showcolors(). Other than that - it works (I should
have run it first rather than composed it while replying). Bob's
response reminded me of where I had seen the chart of colors. But the
showcolors() sub might still be useful if you want the exact mapping
between indices and colors. Sorry for any confusion.

-John Coleman

 

[John Coleman]

Hi

The only problem with this approach is that Excel treats RGB as a
request which it is able to satisfy in only 56 ways.

Try:

Sub Hmm()
Dim R As Long, G As Long, B As Long
R = 153
G = 204
B = 255
Range("A1").Interior.Color = RGB(R, G, B) 'Colorindex 37
MsgBox RGB(R, G, B) & " vs. " & Range("A1").Interior.Color
R = 154
G = 205
B = 254
MsgBox RGB(R, G, B) & " vs. " & Range("A1").Interior.Color
End Sub

You'll see that the 2 numbers are the same in the first message box but
differ in the second. If you want it to satisfy a custom request you
have to teach it how to by modifying its palette.

-John Coleman

 

[John Coleman]

Make that

Sub Hmm()
Dim R As Long, G As Long, B As Long
R = 153
G = 204
B = 255
Range("A1").Interior.Color = RGB(R, G, B)
MsgBox RGB(R, G, B) & " vs. " & Range("A1").Interior.Color
R = 154
G = 205
B = 254
Range("A1").Interior.Color = RGB(R, G, B)
MsgBox RGB(R, G, B) & " vs. " & Range("A1").Interior.Color
End Sub

I inadvertantly edited out the second color assignment
Range("A1").Interior.Color = RGB(R, G, B) (when I removed a comment
that wasn't relevant)
The actual output is exactly the same, emphasizing that the small
changes in the RGB values didn't make any difference (in this case)

Sorry about any confusion

-John Coleman

 

[John Coleman]

Richard,

You need to explicitly change the palette in order to display a color
not on the palette (although I don't know if it strictly *has* to be
before you execute something like Range("A1").Interior.Color =
RGB(12,13,14) if the RHS is not on the palette, though I suspect it
does. I can't imagine why Excel would store more color information for
a cell than it can currently display). It doesn't seem to be
well-documented. I only discovered the limitation when I wanted an
effect that required the shading of cells to range smoothly from black
to white.

-John

 

[RichardSchollar]

John - thanks for your help (took a while replying cos the forum I
usually access this board from seems to have disappeared).

Tom - thanks for the explain too. Thanks to both of you I now know an
awful lot more about colour manipulation in Excel spreadsheets (and the
inherent limitations).

I take it that the 56 colour limit may not apply if you imbed say a
JPEG in the sheet? It doesn't seem to, but that could possibly be down
to my tired old eyes...

Best regards

Richard

 

[John Coleman]

Richard,

Tom can correct me if I am wrong - but the ability to embed JPEG, etc.
into Excel is unaffected by the 56 color limit in the palette. The
limited color indices is, as I understand it, mostly a memory
optimization that allows just one byte for each cell to be devoted to
interior color (though why not 64 colors? Maybe 7 bits for the color
and 1 bit for whether or not there *is* a color?). It would be wasteful
in the extreme for Excel to go into a picture file and modify its
colors. An embedded picture appears as is in its own implicit window.

-John

 

[John Coleman]

One other thing,

If you are curious about the exact default mapping between color
indices and RGB values, copy the following into a new workbook and run
it:

Sub ShowColors()
'Based on fact that
'RGB(R, G, B) = 65536 * B + 256 * G + R
Dim i As Long, C As Long
Dim R As Long, G As Long, B As Long
Range("A1").Value = "Index"
Range("B1").Value = "Color"
Range("C1").Value = "R"
Range("D1").Value = "G"
Range("E1").Value = "B"
Range("F1").Value = "Check"
For i = 1 To 56
Range("A1").Offset(i, 0).Value = i
Range("B1").Offset(i, 0).Interior.ColorIndex = i
C = Range("B1").Offset(i, 0).Interior.Color
R = C Mod 256
G = ((C - R) Mod 65536) / 256
B = (C - 256 * G - R) / 65536
Range("C1").Offset(i, 0).Value = R
Range("D1").Offset(i, 0).Value = G
Range("E1").Offset(i, 0).Value = B
Range("F1").Offset(i, 0).Interior.Color = RGB(R, G, B)
Next i
End Sub

Take care

-John