Creating custom color palettes from worksheet ranges

[Del Cotter]

Can anyone recommend a free utility I can use to populate Excel's color
palette from cells in Excel 95 or 2000? I want to create custom
palettes but I don't want the effort of changing the palette by hand.

I would like to do one of the following

A B C D
1 Color Red Grn Blu
2 Red 1 1 1
3 Green 1 1 1
4 Blue 1 1 1
5 Orange 1 1 1

or

A B C D
1 Color Hue Sat Lum
2 Red 1 1 1
3 Green 1 1 1
4 Blue 1 1 1
5 Orange 1 1 1

 

[Nicky]

Hi

Not sure about an add in, but the following code will reset th
workbook's scheme colors according to the contents of a worksheet.

Assuming your Red Green Blue values are
* in adjacent cells in columns B, C & D
* starting at row 2, ending at row 57 (ie in range B2:57)
* on a sheet named"sheet1"

this code should work:

Sub reset_colors()
res = InputBox("enter number of scheme colors to reset - note, maximu
is 56", "series to reset", 56)

If res <> "" And res <> CInt(res) Then
res = CInt(res)
If res > 56 Then res = 56
MsgBox "only whole numbers of series between 1 and 56 can be changed.
& res & " series will be changed"
End If

If res <> "" And res > 56 Then
MsgBox "the maximum number is 56. 56 colors will be changed"
res = 56
End If
If res <> "" And res < 1 Then
MsgBox "the minimum number is 1. no colors will be changed"
res = ""
End If

For n = 1 To res
red_c = Sheets("sheet1").Rows(n + 1).Columns(2).Value
green_c = Sheets("sheet1").Rows(n + 1).Columns(3).Value
blue_c = Sheets("sheet1").Rows(n + 1).Columns(4).Value

ActiveWorkbook.Colors(n) = RGB(red_c, green_c, blue_c)
Next
End Sub

I have also attached a spreadsheet with the layout and code include

Attachment filename: set colors.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=52701

 

[Del Cotter]

Not sure about an add in, but the following code will reset the
workbook's scheme colors according to the contents of a worksheet.

Thanks for that, it works great. I just have a couple more questions
for the newsgroup:

a) What's a good formula to convert HST to RGB values? It feels like I
ought to be able to work it out myself, (I can see the color cube in my
imagination, and I see how it could work with some trigonometry) but
maybe I'm too tired. If I do get it on my own, I'll post it here.

b) What's up with the placement of the colors in the "Custom colors"
grid? I see that the line and pattern colors are in orderly rows (17-24
and 25-32 respectively), but the custom colors are all over the place.
What, if any, rhyme or reason is there to this arrangement?