Skip to content

Instantly share code, notes, and snippets.

@ncalm

ncalm/excel-lambda-rgb2hex.txt

Last active February 7, 2024 15:31
Show Gist options
  • Save ncalm/265446258d884090651b4d5001bb4a8e to your computer and use it in GitHub Desktop.
Save ncalm/265446258d884090651b4d5001bb4a8e to your computer and use it in GitHub Desktop.
Download ZIP
Resources for retrieving, understanding and using the ColorIndex property in VBA
Raw
excel-lambda-rgb2hex.txt
RGB2HEX = LAMBDA(rgb_array,
LET(
_rgb_array,TOROW(0+rgb_array),
IF(COUNT(_rgb_array)<>3,#VALUE!,
CONCAT("#",DEC2HEX(_rgb_array,2))
)
)
);
Raw
get_vba_colorindex.m
let
Source = Csv.Document(Web.Contents("https://gist.githubusercontent.com/ncalm/265446258d884090651b4d5001bb4a8e/raw/226c712374c0fcd209886cf29de4669d58418822/vba_colorindex.csv"),[Delimiter=",", Columns=6, Encoding=65001, QuoteStyle=QuoteStyle.None]),
Promote = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
Result = Table.TransformColumnTypes(
Promote,
{
{"colorindex", Int64.Type},
{"rgb_string", type text},
{"red", Int64.Type},
{"green", Int64.Type},
{"blue", Int64.Type},
{"hex", type text}
}
)
in
Result
Raw
vba_color_constants.vb
' This code provided by Rick Rothstein, Microsoft Excel MVP from the following article:
' https://www.linkedin.com/pulse/color-constants-use-vba-code-rick-rothstein/
Public Const vbAliceBlue As Long = 1677540
Public Const vbAntiqueWhite As Long = 14150650
Public Const vbAqua As Long = 16776960
Public Const vbAquamarine As Long = 13959039
Public Const vbAzure As Long = 16777200
Public Const vbBeige As Long = 14480885
Public Const vbBisque As Long = 12903679
Public Const vbBlancheDalmond As Long = 13495295
Public Const vbBlueViolet As Long = 14822282
Public Const vbBrown As Long = 2763429
Public Const vbBurlyWood As Long = 8894686
Public Const vbCadetBlue As Long = 10526303
Public Const vbChartReuse As Long = 65407
Public Const vbChocolate As Long = 1993170
Public Const vbCoral As Long = 5275647
Public Const vbCornFlowerBlue As Long = 15570276
Public Const vbCornSilk As Long = 14481663
Public Const vbCream As Long = 15793151
Public Const vbCrimson As Long = 3937500
Public Const vbDarkBlue As Long = 9109504
Public Const vbDarkCyan As Long = 9145088
Public Const vbDarkerGray As Long = 8421504
Public Const vbDarkGreen As Long = 25600
Public Const vbDarkGoldenRod As Long = 755384
Public Const vbDarkGray As Long = 11119017
Public Const vbGreen As Long = 32512
Public Const vbDarkGray As Long = 11119017
Public Const vbDarkKhaki As Long = 7059389
Public Const vbDarkMagenta As Long = 9109643
Public Const vbDarkOliveGreen As Long = 3107669
Public Const vbDarkOrange As Long = 36095
Public Const vbDarkOrchid As Long = 13382297
Public Const vbDarkRed As Long = 139
Public Const vbDarkSalmon As Long = 8034025
Public Const vbDarkSeaGreen As Long = 9419919
Public Const vbDarkSlateBlue As Long = 9125192
Public Const vbDarkSlateGray As Long = 5197615
Public Const vbDarkSlateGrey As Long = 5197615
Public Const vbDarkTurquoise As Long = 13749760
Public Const vbDarkViolet As Long = 13828244
Public Const vbDeepPink As Long = 9639167
Public Const vbDeepSkyBlue As Long = 16760576
Public Const vbDimGray As Long = 4144959
Public Const vbDodgerBlue As Long = 16748574
Public Const vbFireBrick As Long = 2237106
Public Const vbFloralWhite As Long = 15792895
Public Const vbFuchsia As Long = 16711935
Public Const vbGainsBoro As Long = 14474460
Public Const vbGhostWhite As Long = 16775416
Public Const vbGold As Long = 55295
Public Const vbGoldenRod As Long = 2139610
Public Const vbGray As Long = 8355711
Public Const vbGreenYellow As Long = 3145645
Public Const vbHoneyDew As Long = 15794160
Public Const vbHotPink As Long = 11823615
Public Const vbIndianRed As Long = 6053069
Public Const vbIndigo As Long = 8519755
Public Const vbIvory As Long = 15794175
Public Const vbKhaki As Long = 9234160
Public Const vbLavender As Long = 16443110
Public Const vbLavenderBlush As Long = 16118015
Public Const vbLawnGreen As Long = 64636
Public Const vbLegacySkyBlue As Long = 15780518
Public Const vbLemonChiffon As Long = 13499135
Public Const vbLightBlue As Long = 15128749
Public Const vbLightCoral As Long = 8421616
Public Const vbLightCyan As Long = 16777184
Public Const vbLightGrey As Long = 13882323
Public Const vbLightGoldenRodYellow As Long = 13826810
Public Const vbLightGray As Long = 12632256
Public Const vbLightGreen As Long = 9498256
Public Const vbLightPink As Long = 12695295
Public Const vbLightSalmon As Long = 8036607
Public Const vbLightSeaGreen As Long = 11186720
Public Const vbLightSkyBlue As Long = 16436871
Public Const vbLightSlateGray As Long = 10061943
Public Const vbLightSteelBlue As Long = 14599344
Public Const vbLightYellow As Long = 14745599
Public Const vbLime As Long = 65280
Public Const vbLimeGreen As Long = 3329330
Public Const vbLinen As Long = 15134970
Public Const vbMaroon As Long = 127
Public Const vbMediumAquamarine As Long = 11193702
Public Const vbMediumBlue As Long = 13434880
Public Const vbMediumGray As Long = 10789024
Public Const vbMediumOrchid As Long = 13850042
Public Const vbMediumPurple As Long = 14381203
Public Const vbMediumSeaGreen As Long = 7451452
Public Const vbMediumSlateBlue As Long = 15624315
Public Const vbMediumSpringGreen As Long = 10156544
Public Const vbMediumTurquoise As Long = 13422920
Public Const vbMediumVioletRed As Long = 8721863
Public Const vbMidnightBlue As Long = 7346457
Public Const vbMintCream As Long = 16449525
Public Const vbMistyRose As Long = 14804223
Public Const vbMoccasin As Long = 11920639
Public Const vbMoneyGreen As Long = 12639424
Public Const vbNavajoWhite As Long = 11394815
Public Const vbNavy As Long = 8323072
Public Const vbOldLace As Long = 15136253
Public Const vbOlive As Long = 32639
Public Const vbOliveDrab As Long = 2330219
Public Const vbOrange As Long = 42495
Public Const vbOrangeRed As Long = 17919
Public Const vbOrchid As Long = 14053594
Public Const vbPaleGoldenRod As Long = 11200750
Public Const vbPaleGreen As Long = 10025880
Public Const vbPaleTurquoise As Long = 15658671
Public Const vbPaleVioletRed As Long = 9662683
Public Const vbPapayaWhip As Long = 14020607
Public Const vbPeachPuff As Long = 12180223
Public Const vbPeru As Long = 4163021
Public Const vbPlum As Long = 14524637
Public Const vbPowderBlue As Long = 15130800
Public Const vbPurple As Long = 8323199
Public Const vbRosyBrown As Long = 9408444
Public Const vbRoyalBlue As Long = 14772545
Public Const vbSaddleBrown As Long = 1262987
Public Const vbSalmon As Long = 7504122
Public Const vbSandyBrown As Long = 6333684
Public Const vbSeaGreen As Long = 5737262
Public Const vbSeaShell As Long = 15660543
Public Const vbSienna As Long = 2970272
Public Const vbSilver As Long = 12632256
Public Const vbSkyBlue As Long = 15453831
Public Const vbSlateBlue As Long = 13458026
Public Const vbSlateGray As Long = 9470064
Public Const vbSlateGrey As Long = 9470064
Public Const vbSnow As Long = 16448255
Public Const vbSpringGreen As Long = 8388352
Public Const vbSteelBlue As Long = 11829830
Public Const vbTan As Long = 9221330
Public Const vbTeal As Long = 8355584
Public Const vbThistle As Long = 14204888
Public Const vbTomato As Long = 4678655
Public Const vbTurquoise As Long = 13688896
Public Const vbViolet As Long = 15631086
Public Const vbWheat As Long = 11788021
Public Const vbWhiteSmoke As Long = 16119285
Public Const vbYellowGreen As Long = 33294348
Raw
vba_colorindex.csv
colorindex rgb_string red green blue hex
1 RGB(0,0,0) 0 0 0 #000000
2 RGB(255,255,255) 255 255 255 #FFFFFF
3 RGB(255,0,0) 255 0 0 #FF0000
4 RGB(0,255,0) 0 255 0 #00FF00
5 RGB(0,0,255) 0 0 255 #0000FF
6 RGB(255,255,0) 255 255 0 #FFFF00
7 RGB(255,0,255) 255 0 255 #FF00FF
8 RGB(0,255,255) 0 255 255 #00FFFF
9 RGB(128,0,0) 128 0 0 #800000
10 RGB(0,128,0) 0 128 0 #008000
11 RGB(0,0,128) 0 0 128 #000080
12 RGB(128,128,0) 128 128 0 #808000
13 RGB(128,0,128) 128 0 128 #800080
14 RGB(0,128,128) 0 128 128 #008080
15 RGB(192,192,192) 192 192 192 #C0C0C0
16 RGB(128,128,128) 128 128 128 #808080
17 RGB(153,153,255) 153 153 255 #9999FF
18 RGB(153,51,102) 153 51 102 #993366
19 RGB(255,255,204) 255 255 204 #FFFFCC
20 RGB(204,255,255) 204 255 255 #CCFFFF
21 RGB(102,0,102) 102 0 102 #660066
22 RGB(255,128,128) 255 128 128 #FF8080
23 RGB(0,102,204) 0 102 204 #0066CC
24 RGB(204,204,255) 204 204 255 #CCCCFF
25 RGB(0,0,128) 0 0 128 #000080
26 RGB(255,0,255) 255 0 255 #FF00FF
27 RGB(255,255,0) 255 255 0 #FFFF00
28 RGB(0,255,255) 0 255 255 #00FFFF
29 RGB(128,0,128) 128 0 128 #800080
30 RGB(128,0,0) 128 0 0 #800000
31 RGB(0,128,128) 0 128 128 #008080
32 RGB(0,0,255) 0 0 255 #0000FF
33 RGB(0,204,255) 0 204 255 #00CCFF
34 RGB(204,255,255) 204 255 255 #CCFFFF
35 RGB(204,255,204) 204 255 204 #CCFFCC
36 RGB(255,255,153) 255 255 153 #FFFF99
37 RGB(153,204,255) 153 204 255 #99CCFF
38 RGB(255,153,204) 255 153 204 #FF99CC
39 RGB(204,153,255) 204 153 255 #CC99FF
40 RGB(255,204,153) 255 204 153 #FFCC99
41 RGB(51,102,255) 51 102 255 #3366FF
42 RGB(51,204,204) 51 204 204 #33CCCC
43 RGB(153,204,0) 153 204 0 #99CC00
44 RGB(255,204,0) 255 204 0 #FFCC00
45 RGB(255,153,0) 255 153 0 #FF9900
46 RGB(255,102,0) 255 102 0 #FF6600
47 RGB(102,102,153) 102 102 153 #666699
48 RGB(150,150,150) 150 150 150 #969696
49 RGB(0,51,102) 0 51 102 #003366
50 RGB(51,153,102) 51 153 102 #339966
51 RGB(0,51,0) 0 51 0 #003300
52 RGB(51,51,0) 51 51 0 #333300
53 RGB(153,51,0) 153 51 0 #993300
54 RGB(153,51,102) 153 51 102 #993366
55 RGB(51,51,153) 51 51 153 #333399
56 RGB(51,51,51) 51 51 51 #333333
Raw
vba_colorindex_ColorCell.vb
Sub ColorCell()
Dim qryListObj As ListObject
Dim qryRange As Range
Set qry = ThisWorkbook.Worksheets("vba_colorindex").ListObjects("vba_colorindex")
Set qryRange = qry.DataBodyRange
For Each cell In qryRange.Columns(1).Cells
cell.Interior.ColorIndex = cell.Value
Next cell
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment