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

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)

"mark kubicki" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

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

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

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

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

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

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

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

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