Change cell interior color according to the xlrgbcolor name in cell

Rhodie72

Well-known Member
Joined
Apr 18, 2016
Messages
573
I'm stuck here. Can anyone please show me how to use the rgb name in a cell to make the cell adjacent to it change color?
Here's what I've got so far:
Code:
Sub SetColour()
Dim Colour As String, rgbColour() As XlRgbColor, n As Long
    For n = 1 To Range("A1").CurrentRegion.Cells.Count
        Colour = "rgb" + ActiveCell.Value
        ActiveCell.Offset(n, 1).Interior.Color = rgbColour(Colour)
    Next n
End Sub
Below is the list of names used but the colours are oddly named and what the heck does Bisque look like in a hurry????:ROFLMAO: vbBlue and vbYellow are easily understood but the rest, well... an
Code:
InteriorDecorator.woz.here
Maybe it needs to be processed via a custom function call?
AliceBlue
AntiqueWhite
Aqua
Aquamarine
Azure
Beige
Bisque
Black
BlanchedAlmond
Blue
BlueViolet
Brown
BurlyWood
CadetBlue
Chartreuse
Coral
CornflowerBlue
Cornsilk
Crimson
DarkBlue
DarkCyan
DarkGoldenrod
DarkGray
DarkGreen
DarkGrey
DarkKhaki
DarkMagenta
DarkOliveGreen
DarkOrange
DarkOrchid
DarkRed
DarkSalmon
DarkSeaGreen
DarkSlateBlue
DarkSlateGray
DarkSlateGrey
DarkTurquoise
DarkViolet
DeepPink
DeepSkyBlue
DimGray
DimGrey
DodgerBlue
FireBrick
FloralWhite
ForestGreen
Fuschia
Gainsboro
GhostWhite
Gold
Goldenrod
Gray
Green
GreenYellow
Grey
Honeydew
HotPink
IndianRed
Indigo
Ivory
Khaki
Lavender
LavenderBlush
LawnGreen
LemonChiffon
LightBlue
LightCoral
LightCyan
LightGoldenrodYellow
LightGray
LightGreen
LightGrey
LightPink
LightSalmon
LightSeaGreen
LightSkyBlue
LightSlateGray
LightSlateGrey
LightSteelBlue
LightYellow
Lime
LimeGreen
Linen
Maroon
MediumAquamarine
MediumBlue
MediumOrchid
MediumPurple
MediumSeaGreen
MediumSlateBlue
MediumSpringGreen
MediumTurquoise
MediumVioletRed
MidnightBlue
MintCream
MistyRose
Moccasin
NavajoWhite
Navy
NavyBlue
OldLace
Olive
OliveDrab
OrangeRed
Orchid
PaleGoldenrod
PaleGreen
PaleTurquoise
PaleVioletRed
PapayaWhip
PeachPuff
Peru
Pink
Plum
PowderBlue
Purple
Red
RosyBrown
RoyalBlue
Salmon
SandyBrown
SeaGreen
Seashell
Sienna
Silver
SkyBlue
SlateBlue
SlateGray
SlateGrey
Snow
SpringGreen
SteelBlue
Tan
Teal
Thistle
Tomato
Turquoise
Violet
Wheat
White
WhiteSmoke
Yellow
YellowGreen

<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Why not just google the Hex codes for each of those colours... It took less than 30 seconds. Copy them into Column B and then use that number (instead of the word in column A) to display your colours.
 
Last edited:
Upvote 0
Code:
Sub ColorColumnC()
For Each C In Range("B1:B145")
    With Application.WorksheetFunction
        C.Offset(, 1).Interior.Color = RGB(.Hex2Dec(Mid(C.Value, 2, 2)), .Hex2Dec(Mid(C.Value, 4, 2)), .Hex2Dec(Right(C.Value, 2)))
    End With
Next C
End Sub
 
Upvote 0
Why not just google the Hex codes for each of those colours... It took less than 30 seconds. Copy them into Column B and then use that number (instead of the word in column A) to display your colours.

I have a project to complete that requires this particular approach. It is unavoidable from the client's perspective. There's a lot of other code already in the existing project that cannot be changed that easily within the budget. The principle is that if this can be done then an array can be created for custom colors expanded upon this collection. This only has 143, to be expanded to 63,000. Darn Interior.Decorator.Colors lol
Once that's been completed we will expand it to the full gamut of over 16 million colors. Not looking forward to that.
 
Upvote 0
There are only 8 colours that have "names" in VBA... and 56 numbered colours. Anything outside that range of colours you need to use something like RGB or HEX, unfortunately.

I do not see the harm in making a Giant table of colours and their corresponding RGB values or HEX value on a separate sheet, And then reference it with the name using a lookup function like index match or vlookup... You can even hide that sheet...

To be honest, I am not sure what you are trying to achieve... Are you planning on naming 16 million colours with unique names and then having someone recall some of those names to produce the exact colours they want? It sounds monumentally inefficient.
 
Upvote 0
There are only 8 colours that have "names" in VBA... and 56 numbered colours. Anything outside that range of colours you need to use something like RGB or HEX, unfortunately.

To be honest, I am not sure what you are trying to achieve... Are you planning on naming 16 million colours with unique names and then having someone recall some of those names to produce the exact colours they want? It sounds monumentally inefficient.

Firstly:
There are 143 named colours in the xlrgbcolor collection, 16 in the vb collection and 16.7 million in the hex and RGB(0,0,0) specifications

Secondly:
Yes, Lets think of this as a paint manufacturer that has named several paints throughout its existence. A paint made in 1973 has a different name to the one used today. The manufacturer wants to use an optical recognition device that picks a colour and attaches a name to it from a database of names it created over its 79 year lifespan. There are about 58 shades of whites for Interior.Decorator.Uses and they all have names. The RGB values are what the scanner picks up but people want the name.

The paint manufacturer sends out a device to the customer that calibrates against their wall, gets the name of the paint and then the customer can select from a screen output of the new colour they want to pain their wall and see if it is suitable without buying a single drop of paint. The database of colour names is set up in Excel and a colour match app is used to transfer it to the android device. The manufacturer wants to name every visible colour in the spectrum.

That's only 17 columns in Excel with a million rows of colour. Many have names but many more do not. Duplication of names is not wanted, hence the metrication of the colour naming system.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top