design Color Palette

[Katie]

I'm working with VB in Excel 2003, and I need more colors than are
offered on the default palette. Does anyone know if and where I might
be able to get a broader range of colors and their codes? Here's what
my program looks like:

Sub ColorTest()

'Changes cell color based on adjacent to the left cell value
j = 2
While j < 7
i = 2
While ActiveSheet.Cells(i, j - 1) <> ""
ActiveSheet.Cells(i, j).Interior.ColorIndex = Switch( _
ActiveSheet.Cells(i, j - 1) < 0.5, 3, _
ActiveSheet.Cells(i, j - 1) < 1, 9, _
ActiveSheet.Cells(i, j - 1) < 1.5, 6, _
ActiveSheet.Cells(i, j - 1) < 2, 12, _
ActiveSheet.Cells(i, j - 1) < 2.5, 4, _
ActiveSheet.Cells(i, j - 1) < 3, 10, _
ActiveSheet.Cells(i, j - 1) < 3.5, 50, _
ActiveSheet.Cells(i, j - 1) < 4, 8, _
ActiveSheet.Cells(i, j - 1) < 4.5, 5, _
ActiveSheet.Cells(i, j - 1) < 5, 11, _
ActiveSheet.Cells(i, j - 1) < 5.5, 7, _
ActiveSheet.Cells(i, j - 1) < 6, 13, _
ActiveSheet.Cells(i, j - 1) < 6.5, 15, _
ActiveSheet.Cells(i, j - 1) < 7, 16)
i = i + 1
Wend
j = j + 2
Wend

End Sub

[Jason]

Not the cleanest layout, but great information:

http://www.mvps.org/dmcritchie/excel/colors.htm

"Katie" wrote:

> I'm working with VB in Excel 2003, and I need more colors than are
> offered on the default palette. Does anyone know if and where I might
> be able to get a broader range of colors and their codes? Here's what
> my program looks like:
>
> Sub ColorTest()
>
> 'Changes cell color based on adjacent to the left cell value
> j = 2
> While j < 7
> i = 2
> While ActiveSheet.Cells(i, j - 1) <> ""
> ActiveSheet.Cells(i, j).Interior.ColorIndex = Switch( _
> ActiveSheet.Cells(i, j - 1) < 0.5, 3, _
> ActiveSheet.Cells(i, j - 1) < 1, 9, _
> ActiveSheet.Cells(i, j - 1) < 1.5, 6, _
> ActiveSheet.Cells(i, j - 1) < 2, 12, _
> ActiveSheet.Cells(i, j - 1) < 2.5, 4, _
> ActiveSheet.Cells(i, j - 1) < 3, 10, _
> ActiveSheet.Cells(i, j - 1) < 3.5, 50, _
> ActiveSheet.Cells(i, j - 1) < 4, 8, _
> ActiveSheet.Cells(i, j - 1) < 4.5, 5, _
> ActiveSheet.Cells(i, j - 1) < 5, 11, _
> ActiveSheet.Cells(i, j - 1) < 5.5, 7, _
> ActiveSheet.Cells(i, j - 1) < 6, 13, _
> ActiveSheet.Cells(i, j - 1) < 6.5, 15, _
> ActiveSheet.Cells(i, j - 1) < 7, 16)
> i = i + 1
> Wend
> j = j + 2
> Wend
>
> End Sub
>
>

[Peter T]

When you say you need "more" or a "broader range" of colours, if you mean
different to the default 46, palette colours can of course be customized to
any RGB colour. See the link referred by Jason to David McRitcie's
comprehensive page.

However if you mean greater quantity, or you don't want to customize the
default palette, you can still create 2-3000 pseudo unique colours with the
default palette by mixing 2 colours as 25, 50 or 75% pattern shades. It's
not easy to find the correct combinations, but if this is what you need send
me the RGB colours you require and I'll send you the best matches, ie
color-index pairs + shade. (I have an app that colour matches and returns
calculated best combinations).

Regards,
Peter T
pmbthornton gmail com


"Katie" <[email protected]> wrote in message
news:[email protected]...
> I'm working with VB in Excel 2003, and I need more colors than are
> offered on the default palette. Does anyone know if and where I might
> be able to get a broader range of colors and their codes? Here's what
> my program looks like:
>
> Sub ColorTest()
>
> 'Changes cell color based on adjacent to the left cell value
> j = 2
> While j < 7
> i = 2
> While ActiveSheet.Cells(i, j - 1) <> ""
> ActiveSheet.Cells(i, j).Interior.ColorIndex = Switch( _
> ActiveSheet.Cells(i, j - 1) < 0.5, 3, _
> ActiveSheet.Cells(i, j - 1) < 1, 9, _
> ActiveSheet.Cells(i, j - 1) < 1.5, 6, _
> ActiveSheet.Cells(i, j - 1) < 2, 12, _
> ActiveSheet.Cells(i, j - 1) < 2.5, 4, _
> ActiveSheet.Cells(i, j - 1) < 3, 10, _
> ActiveSheet.Cells(i, j - 1) < 3.5, 50, _
> ActiveSheet.Cells(i, j - 1) < 4, 8, _
> ActiveSheet.Cells(i, j - 1) < 4.5, 5, _
> ActiveSheet.Cells(i, j - 1) < 5, 11, _
> ActiveSheet.Cells(i, j - 1) < 5.5, 7, _
> ActiveSheet.Cells(i, j - 1) < 6, 13, _
> ActiveSheet.Cells(i, j - 1) < 6.5, 15, _
> ActiveSheet.Cells(i, j - 1) < 7, 16)
> i = i + 1
> Wend
> j = j + 2
> Wend
>
> End Sub
>

[Lonnie M.]

Here is an example assigning the rgb value:

ActiveCell.Interior.Color = RGB(255, 0, 0)

HTH--Lonnie M.

[Katie]

Thanks for the help, everyone. I didn't realize that you could use RGB
values so easily. That made it a lot easier!

[Katie]

Thanks everyone. I didn't realize I could just use RGB values. That
really helped!!

[Katie]

Thanks Peter! I would really appreciate it if you were able to do it.
I also sent a copy to your gmail account. Here are the colors I need:
RGB (139, 139, 255)
RGB(113, 113, 255)
RGB(88, 88, 255)
RGB(36, 36, 255)
RGB(11, 11, 255)
RGB(0, 0, 240)
RGB(0, 0, 215)
RGB(0, 0, 189)
RGB(0, 0, 164)
RGB(0, 0, 139)
RGB(0, 0, 113)
RGB(0, 0, 88)
RGB(0, 0, 61)
RGB(0, 0, 11)

[Peter T]

Hi Katie,

Try this in a Workbook with a default palette, or at least don't customize
any of the colour index's listed in vIntclr or vPatClr arrays.

Sub myBlues()
Dim vIntclr
Dim vPat
Dim vPatClr
Dim vRGB
Dim sh As Shape

'cells with two colours & pattern shades

vIntclr = Array(17, 5, 5, 5, 5, 5, 5, 5, 5, 11, 1, 1, 1, 1)

vPat = Array(xlGray25, xlGray75, xlGray50, xlGray50, xlSolid, _
xlGray25, xlGray25, xlGray50, xlGray75, xlSolid, _
xlGray75, xlGray50, xlGray25, xlSolid)

vPatClr = Array(41, 17, 17, 41, -4105, 49, 11, _
11, 11, -4105, 11, 11, 11, -4105)

For i = 0 To 13
With Cells(i + 1, 2).Interior
.ColorIndex = vIntclr(i)
.Pattern = vPat(i)
.PatternColorIndex = vPatClr(i)
End With
Next

'compare with original colors

vRGB = Array(16747403, 16740721, 16734296, 16720932, 16714507, _
15728640, 14090240, 12386304, 10747904, 9109504, _
7405568, 5767168, 3997696, 720896, 10053222, 8421504)

For i = 1 To 14
With Cells(i, 1)
Set sh = ActiveSheet.Shapes.AddShape(1, _
.Left, .Top, .Width, .Height)
sh.Fill.ForeColor.RGB = vRGB(i - 1)
End With
Next

End Sub

This Sub formats cells in col-B with 2-colour + pattern shade combinations.
For comparison it also adds rectangles in col-A with the your original RGB
colours. Not a perfect match but not bad considering the limited palette and
using only default colours.

If you are not averse to customizing the palette then why not use the chart
colours.

Sub CustomBlues()
Dim i As Long
Dim vRGB
vRGB = Array(16747403, 16740721, 16734296, 16720932, 16714507, _
15728640, 14090240, 12386304, 10747904, 9109504, _
7405568, 5767168, 3997696, 720896, 10053222, 8421504)

For i = 0 To 13
ActiveWorkbook.Colors(i + 17) = vRGB(i)
Next

End Sub

The chart colours (colorindex's 17-32) are not visible in the drop down
palette but simply-
activecell.interior.colorindex = 17

The numbers in vRGB are long colour numbers, same as the RGB colours you
posted.

Regards,
Peter T


"Katie" <[email protected]> wrote in message
news:[email protected]...
> Thanks Peter! I would really appreciate it if you were able to do it.
> I also sent a copy to your gmail account. Here are the colors I need:
> RGB (139, 139, 255)
> RGB(113, 113, 255)
> RGB(88, 88, 255)
> RGB(36, 36, 255)
> RGB(11, 11, 255)
> RGB(0, 0, 240)
> RGB(0, 0, 215)
> RGB(0, 0, 189)
> RGB(0, 0, 164)
> RGB(0, 0, 139)
> RGB(0, 0, 113)
> RGB(0, 0, 88)
> RGB(0, 0, 61)
> RGB(0, 0, 11)
>