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

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

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

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

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

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