custom cell color
 

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

custom cell color
 

You can only use a colour from the colour palette, but you can change the
colour in there. ToolsOptionsColorModify.

--

HTH

Bob Phillips

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

"mark kubicki" wrote in message
...
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

custom cell color
 

Mark

Range("A1").Interior.Color = RGB(255,0,0)

adjust RGB value to suit.

Best regards

Richar

--
RichardScholla
-----------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=524
View this thread: http://www.officehelp.in/showthread.php?t=127501

Posted from - http://www.officehelp.i

custom cell color
 

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

mark kubicki wrote:
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

custom cell color
 

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 wrote:
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

mark kubicki wrote:
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

custom cell color
 

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

RichardSchollar wrote:
Mark

Range("A1").Interior.Color = RGB(255,0,0)

adjust RGB value to suit.

Best regards

Richard


--
RichardSchollar
------------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=5248
View this thread: http://www.officehelp.in/showthread.php?t=1275010

Posted from - http://www.officehelp.in

custom cell color
 

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 wrote:
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

RichardSchollar wrote:
Mark

Range("A1").Interior.Color = RGB(255,0,0)

adjust RGB value to suit.

Best regards

Richard


--
RichardSchollar
------------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=5248
View this thread: http://www.officehelp.in/showthread.php?t=1275010

Posted from - http://www.officehelp.in

custom cell color
 

John

I think I get it - so what you're saying is that you need to update th
palette *before* applying it to a sheet? Otherwise, the applied colo
will default to, I presume, the closest match from the existin
palette? Am I understanding your point correctly?

Thanks & kind regards

Richar

--
RichardScholla
-----------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=524
View this thread: http://www.officehelp.in/showthread.php?t=127501

Posted from - http://www.officehelp.i

custom cell color
 

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 wrote:
John

I think I get it - so what you're saying is that you need to update the
palette *before* applying it to a sheet? Otherwise, the applied color
will default to, I presume, the closest match from the existing
palette? Am I understanding your point correctly?

Thanks & kind regards

Richard


--
RichardSchollar
------------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=5248
View this thread: http://www.officehelp.in/showthread.php?t=1275010

Posted from - http://www.officehelp.in

custom cell color
 

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



Tom Ogilvy wrote:
There would be no advantage to restricting the cell to 56 colors if Excel
retained the RGB colors. It only stores the colorindex - which it must do
for every cell it maintains information about.

So you would need to change the palette first.

--
Regards,
Tom Ogilvy


"John Coleman" wrote:

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 wrote:
John

I think I get it - so what you're saying is that you need to update the
palette *before* applying it to a sheet? Otherwise, the applied color
will default to, I presume, the closest match from the existing
palette? Am I understanding your point correctly?

Thanks & kind regards

Richard


--
RichardSchollar
------------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=5248
View this thread: http://www.officehelp.in/showthread.php?t=1275010

Posted from - http://www.officehelp.in

custom cell color
 

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

RichardSchollar wrote:
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



Tom Ogilvy wrote:
There would be no advantage to restricting the cell to 56 colors if Excel
retained the RGB colors. It only stores the colorindex - which it must do
for every cell it maintains information about.

So you would need to change the palette first.

--
Regards,
Tom Ogilvy


"John Coleman" wrote:

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 wrote:
John

I think I get it - so what you're saying is that you need to update the
palette *before* applying it to a sheet? Otherwise, the applied color
will default to, I presume, the closest match from the existing
palette? Am I understanding your point correctly?

Thanks & kind regards

Richard


--
RichardSchollar
------------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=5248
View this thread: http://www.officehelp.in/showthread.php?t=1275010

Posted from - http://www.officehelp.in

custom cell color
 

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

RichardSchollar wrote:
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



Tom Ogilvy wrote:
There would be no advantage to restricting the cell to 56 colors if Excel
retained the RGB colors. It only stores the colorindex - which it must do
for every cell it maintains information about.

So you would need to change the palette first.

--
Regards,
Tom Ogilvy


"John Coleman" wrote:

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 wrote:
John

I think I get it - so what you're saying is that you need to update the
palette *before* applying it to a sheet? Otherwise, the applied color
will default to, I presume, the closest match from the existing
palette? Am I understanding your point correctly?

Thanks & kind regards

Richard


--
RichardSchollar
------------------------------------------------------------------------
RichardSchollar's Profile: http://www.officehelp.in/member.php?userid=5248
View this thread: http://www.officehelp.in/showthread.php?t=1275010

Posted from - http://www.officehelp.in