Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,615
- Office Version
- 365
- Platform
- Windows
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
VBA - Interior Color to HTML RGB Hex Code
- Thread starter Scott Huish
- Start date
-
- Tags
- code hex interior.color returns rgb
Excel Facts
Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,615
- Office Version
- 365
- Platform
- Windows
I found some code which I modified a little bit, in case anyone needs it:
Code:
Function ColortoWebHex(lColor As Long) As String
Dim iRed, iGreen, iBlue
'Convert Decimal Color Code to RGB
iRed = (lColor Mod 256)
iGreen = (lColor \ 256) Mod 256
iBlue = (lColor \ 65536) Mod 256
'Return RGB Code
ColortoWebHex = "#" & Application.WorksheetFunction.Dec2Hex(iRed, 2) & Application.WorksheetFunction.Dec2Hex(iGreen, 2) & Application.WorksheetFunction.Dec2Hex(iBlue, 2)
End Function
Upvote
0
Norie
Well-known Member
- Joined
- Apr 28, 2004
- Messages
- 76,377
- Office Version
- 365
- Platform
- Windows
Scott
Try this function, pass the cell with the interior colour you want the hex RGB for to it.
Try this function, pass the cell with the interior colour you want the hex RGB for to it.
Code:
Function ColToHexRGB(rng As Range) As String
Dim lngColor As Long
lngColor = rng.Interior.Color
ColToHexRGB = Hex((lngColor Mod 256)) & Hex(((lngColor \ 256) Mod 256)) & Hex((lngColor \ 65536))
End Function
Upvote
0
Scott Huish
MrExcel MVP
- Joined
- Mar 17, 2004
- Messages
- 20,615
- Office Version
- 365
- Platform
- Windows
That doesn't quite work. I put a background in of RGB(255,0,0) and it returned FF00, it should return FF0000.
Upvote
0
Leith Ross
Well-known Member
- Joined
- Mar 17, 2008
- Messages
- 1,874
- Office Version
- 2010
- 2007
- Platform
- Windows
Hello Scott,
I have this macro which does the reverse of what you want. I don't have time to recode it tonight. You should be able to "reverse engineer" what I have done for your needs.
Macro Test
I have this macro which does the reverse of what you want. I don't have time to recode it tonight. You should be able to "reverse engineer" what I have done for your needs.
Rich (BB code):
' Written: January 19, 2012
' Author: Leith Ross
' Summary: Converts standard Web color values and names to Windows RGB colors.
Private WebColorNames As Object
Function ConvertWebColor(ByVal WebColor As String) As Long
' Convert Web hexadecimal RGB values into Windows RGB values
Dim b As Byte
Dim g As Byte
Dim r As Byte
If WebColorNames Is Nothing Then LoadWebColorNames
If Left(WebColor, 1) <> "#" Then
WebColor = WebColorNames(WebColor)
End If
WebColor = Mid(WebColor, 2, Len(WebColor) - 1)
For J = 1 To 6
n = InStr(1, "0123456789ABCDEF", Mid(WebColor, J, 1), vbTextCompare) - 1
n = (16 ^ (J Mod 2)) * n
Select Case J
Case 1, 2: r = r + n
Case 3, 4: g = g + n
Case 5, 6: b = b + n
End Select
Next J
ConvertWebColor = rgb(r, g, b)
End Function
Sub LoadWebColorNames()
Set WebColorNames = CreateObject("Scripting.Dictionary")
WebColorNames.CompareMode = vbTextCompare
With WebColorNames
.Add "AliceBlue", "#F0F8FF"
.Add "AntiqueWhite", "#FAEBD7"
.Add "Aqua", "#00FFFF"
.Add "Aquamarine", "#7FFFD4"
.Add "Azure", "#F0FFFF"
.Add "Beige", "#F5F5DC"
.Add "Bisque", "#FFE4C4"
.Add "Black", "#000000"
.Add "BlanchedAlmond", "#FFEBCD"
.Add "Blue", "#0000FF"
.Add "BlueViolet", "#8A2BE2"
.Add "Brown", "#A52A2A"
.Add "BurlyWood", "#DEB887"
.Add "CadetBlue", "#5F9EA0"
.Add "Chartreuse", "#7FFF00"
.Add "Chocolate", "#D2691E"
.Add "Coral", "#FF7F50"
.Add "CornflowerBlue", "#6495ED"
.Add "Cornsilk", "#FFF8DC"
.Add "Crimson", "#DC143C"
.Add "Cyan", "#00FFFF"
.Add "DarkBlue", "#00008B"
.Add "DarkCyan", "#008B8B"
.Add "DarkGoldenRod", "#B8860B"
.Add "DarkGray", "#A9A9A9"
.Add "DarkGrey", "#A9A9A9"
.Add "DarkGreen", "#006400"
.Add "DarkKhaki", "#BDB76B"
.Add "DarkMagenta", "#8B008B"
.Add "DarkOliveGreen", "#556B2F"
.Add "Darkorange", "#FF8C00"
.Add "DarkOrchid", "#9932CC"
.Add "DarkRed", "#8B0000"
.Add "DarkSalmon", "#E9967A"
.Add "DarkSeaGreen", "#8FBC8F"
.Add "DarkSlateBlue", "#483D8B"
.Add "DarkSlateGray", "#2F4F4F"
.Add "DarkSlateGrey", "#2F4F4F"
.Add "DarkTurquoise", "#00CED1"
.Add "DarkViolet", "#9400D3"
.Add "DeepPink", "#FF1493"
.Add "DeepSkyBlue", "#00BFFF"
.Add "DimGray", "#696969"
.Add "DimGrey", "#696969"
.Add "DodgerBlue", "#1E90FF"
.Add "FireBrick", "#B22222"
.Add "FloralWhite", " #FFFAF0 "
.Add "ForestGreen", " #228B22 "
.Add "Fuchsia", "#FF00FF"
.Add "Gainsboro", "#DCDCDC"
.Add "GhostWhite", "#F8F8FF"
.Add "Gold", " #FFD700 "
.Add "GoldenRod", "#DAA520"
.Add "Gray", "#808080"
.Add "Grey", "#808080"
.Add "Green", "#008000"
.Add "GreenYellow", "#ADFF2F"
.Add "HoneyDew", "#F0FFF0"
.Add "HotPink", "#FF69B4"
.Add "IndianRed", "#CD5C5C"
.Add "Indigo", "#4B0082"
.Add "Ivory", "#FFFFF0"
.Add "Khaki", "#F0E68C"
.Add "Lavender", "#E6E6FA"
.Add "LavenderBlush", "#FFF0F5"
.Add "LawnGreen", "#7CFC00"
.Add "LemonChiffon", "#FFFACD"
.Add "LightBlue", "#ADD8E6"
.Add "LightCoral", "#F08080"
.Add "LightCyan", "#E0FFFF"
.Add "LightGoldenRodYellow", "#FAFAD2"
.Add "LightGray", "#D3D3D3"
.Add "LightGrey", "#D3D3D3"
.Add "LightGreen", "#90EE90"
.Add "LightPink", "#FFB6C1"
.Add "LightSalmon", "#FFA07A"
.Add "LightSeaGreen", "#20B2AA"
.Add "LightSkyBlue", "#87CEFA"
.Add "LightSlateGray", "#778899"
.Add "LightSlateGrey", "#778899"
.Add "LightSteelBlue", "#B0C4DE"
.Add "LightYellow", "#FFFFE0"
.Add "Lime", " #00FF00 "
.Add "LimeGreen", "#32CD32"
.Add "Linen", "#FAF0E6"
.Add "Magenta", "#FF00FF"
.Add "Maroon", "#800000"
.Add "MediumAquaMarine", "#66CDAA"
.Add "MediumBlue", "#0000CD"
.Add "MediumOrchid", "#BA55D3"
.Add "MediumPurple", "#9370D8"
.Add "MediumSeaGreen", "#3CB371"
.Add "MediumSlateBlue", "#7B68EE"
.Add "MediumSpringGreen", "#00FA9A"
.Add "MediumTurquoise", "#48D1CC"
.Add "MediumVioletRed", "#C71585"
.Add "MidnightBlue", "#191970"
.Add "MintCream", "#F5FFFA"
.Add "MistyRose", "#FFE4E1"
.Add "Moccasin", "#FFE4B5"
.Add "NavajoWhite", "#FFDEAD"
.Add "Navy", "#000080"
.Add "OldLace", " #FDF5E6 "
.Add "Olive", "#808000"
.Add "OliveDrab", "#6B8E23"
.Add "Orange", "#FFA500"
.Add "OrangeRed", "#FF4500"
.Add "Orchid", "#DA70D6"
.Add "PaleGoldenRod", "#EEE8AA"
.Add "PaleGreen", "#98FB98"
.Add "PaleTurquoise", "#AFEEEE"
.Add "PaleVioletRed", "#D87093"
.Add "PapayaWhip", "#FFEFD5"
.Add "PeachPuff", "#FFDAB9"
.Add "Peru", "#CD853F"
.Add "Pink", "#FFC0CB"
.Add "Plum", "#DDA0DD"
.Add "PowderBlue", "#B0E0E6"
.Add "Purple", "#800080"
.Add "Red", "#FF0000"
.Add "RosyBrown", "#BC8F8F"
.Add "RoyalBlue", "#4169E1"
.Add "SaddleBrown", "#8B4513"
.Add "Salmon", "#FA8072"
.Add "SandyBrown", "#F4A460"
.Add "SeaGreen", "#2E8B57"
.Add "SeaShell", "#FFF5EE"
.Add "Sienna", "#A0522D"
.Add "Silver", "#C0C0C0"
.Add "SkyBlue", "#87CEEB"
.Add "SlateBlue", "#6A5ACD"
.Add "SlateGray", "#708090"
.Add "SlateGrey", "#708090"
.Add "Snow", "#FFFAFA"
.Add "SpringGreen", "#00FF7F"
.Add "SteelBlue", "#4682B4"
.Add "Tan", "#D2B48C"
.Add "Teal", "#008080"
.Add "Thistle", "#D8BFD8"
.Add "Tomato", "#FF6347"
.Add "Turquoise", "#40E0D0"
.Add "Violet", "#EE82EE"
.Add "Wheat", "#F5DEB3"
.Add "White", "#FFFFFF"
.Add "WhiteSmoke", "#F5F5F5"
.Add "Yellow", "#FFFF00"
.Add "YellowGreen", "#9ACD32"
End With
End SubMacro Test
Rich (BB code):
Sub ColorTest()
x = Hex(ConvertWebColor("Wheat"))
End Sub
Upvote
0
