Working with Colors in a Macro

by Allen Wyatt
(last updated June 27, 2014)

2

Jerri is trying to reference rather simple colors in a macro. In older versions of Excel the color references were understandable, but she's having problems with colors since the introduction of themes. If she records a macro, she gets references to various properties, such as Pattern, PatternColorIndex, TintAndShade, PatternTintAndShade, and ThemeColor. Jerri can't make sense of these and wonders if there is a good reference available that provides the background information she needs to work with colors in a macro.

You aren't alone, Jerri. The new color schemes introduced in Excel 2007 seem to baffle many people. The simple approach to colors used in older versions of Excel is gone, and the new approach doesn't seem to have any humanly discernable rhyme or reason. Doing simple searches at Microsoft doesn't turn up any helpful information. For instance, a search of Microsoft online support for PatternTintAndShade (which should get right to the heart of the matter) returns nothing—and this is three years after Excel 2007 was released! A wider search for the same word, looking at all of Microsoft.com, returns something only marginally more useful; it returns a page that describes the PatternTintAndShade property as a property that "returns or sets a tint and shade pattern." The reaction that comes to mind is "well, duh!"

In searching around the web there are lots of questions that crop up very similar to yours and precious few answers. There was one site that has a bit of useful information and is well worth reading:

http://www.databison.com/index.php/excel-color-palette-and-color-index-change-using-vba/

Sorry about the long URL; you'll want to make sure you get it all. Perhaps, as time goes on, more information will come available.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7883) applies to Microsoft Excel 2007.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Moving and Selecting Rows

If you need to move down a row and then select that row, you may wonder if there is a shortcut to handle such a navigation ...

Discover More

Turning Off Automatic Capitalization

Type some information into a worksheet, and you may notice that Excel automatically capitalizes some of your information. ...

Discover More

Easily Adding a Graphic to a Document

Need to spice up your document with a graphic? Here's the quickest way we've found to get those graphics where you want them.

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Recovering Macros from Corrupted Workbooks

Workbooks get corrupted from time to time; that's a fact of life in an Excel world. If those corrupted workbooks contain ...

Discover More

Bypassing the BeforeClose Event

Hold down the Shift key as you open a workbook, and Excel bypasses any "startup macros" that may be in the workbook. If you ...

Discover More

Relative VBA Selections

Need to select a cell using a macro? Need that selection to be relative to the cell you currently have selected? Here's the ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 0 + 7?

2014-06-30 09:24:02

Bryan

After a few minutes of fiddling around in the immediate window and exploring the MSDN, I've determined the following:

* Old methods for changing the color (Range.Interior.Color) still work as normal. If you don't want to fiddle with the "new" ways, just use what you were using before.

* TintAndShade is a number ranging from -1 (darkest, black) to +1 (lightest, white) that changes the tint of the color. In the color dialogue, this is the same as going to More Colors > Custom, then using the slider along the right hand side to make the color darker or lighter.

* ThemeColor relates to the "Theme Color" portion of the color picker. The number ranges from 1 to 12 (I believe), and the number corresponds to the column in the Theme Color dialogue. For instance, using the standard office theme colors, 1 is White, 4 is Dark Blue, and 8 is Purple.

* You combine TintAndShade with ThemeColor to get any of the colors shown in the Theme Color portion of the color picker: hover over any color and it will say "darker x%" or "lighter x%". For instance, to get the medium red option (red, lighter 40%), set ThemeColor = 6 and TintAndShade = .4.

* Pattern relates to the fill pattern you can choose in the Format Cells dialogue under the Fill option. Specifically, Pattern relates to the Pattern Style dropdown. To figure out what values to use, look up "xlPattern enumeration"

* PatternColorIndex, PatternTintAndShade are the ColorIndex and TintAndShade properties for the pattern, respectively.


2014-06-27 10:15:09

Scott Renz

I have decided to just use the RGB color enumerations:

Name
Value
Description

rgbAliceBlue
16775408
Alice Blue

rgbAntiqueWhite
14150650
Antique White

rgbAqua
16776960
Aqua

rgbAquamarine
13959039
Aquamarine

rgbAzure
16777200
Azure

rgbBeige
14480885
Beige

rgbBisque
12903679
Bisque

rgbBlack
0
Black

rgbBlanchedAlmond
13495295
Blanched Almond

rgbBlue
16711680
Blue

rgbBlueViolet
14822282
Blue Violet

rgbBrown
2763429
Brown

rgbBurlyWood
8894686
Burly Wood

rgbCadetBlue
10526303
Cadet Blue

rgbChartreuse
65407
Chartreuse

rgbCoral
5275647
Coral

rgbCornflowerBlue
15570276
Cornflower Blue

rgbCornsilk
14481663
Cornsilk

rgbCrimson
3937500
Crimson

rgbDarkBlue
9109504
Dark Blue

rgbDarkCyan
9145088
Dark Cyan

rgbDarkGoldenrod
755384
Dark Goldenrod

rgbDarkGray
11119017
Dark Gray

rgbDarkGreen
25600
Dark Green

rgbDarkGrey
11119017
Dark Grey

rgbDarkKhaki
7059389
Dark Khaki

rgbDarkMagenta
9109643
Dark Magenta

rgbDarkOliveGreen
3107669
Dark Olive Green

rgbDarkOrange
36095
Dark Orange

rgbDarkOrchid
13382297
Dark Orchid

rgbDarkRed
139
Dark Red

rgbDarkSalmon
8034025
Dark Salmon

rgbDarkSeaGreen
9419919
Dark Sea Green

rgbDarkSlateBlue
9125192
Dark Slate Blue

rgbDarkSlateGray
5197615
Dark Slate Gray

rgbDarkSlateGrey
5197615
Dark Slate Grey

rgbDarkTurquoise
13749760
Dark Turquoise

rgbDarkViolet
13828244
Dark Violet

rgbDeepPink
9639167
Deep Pink

rgbDeepSkyBlue
16760576
Deep Sky Blue

rgbDimGray
6908265
Dim Gray

rgbDimGrey
6908265
Dim Grey

rgbDodgerBlue
16748574
Dodger Blue

rgbFireBrick
2237106
Fire Brick

rgbFloralWhite
15792895
Floral White

rgbForestGreen
2263842
Forest Green

rgbFuchsia
16711935
Fuchsia

rgbGainsboro
14474460
Gainsboro

rgbGhostWhite
16775416
Ghost White

rgbGold
55295
Gold

rgbGoldenrod
2139610
Goldenrod

rgbGray
8421504
Gray

rgbGreen
32768
Green

rgbGreenYellow
3145645
Green Yellow

rgbGrey
8421504
Grey

rgbHoneydew
15794160
Honeydew

rgbHotPink
11823615
Hot Pink

rgbIndianRed
6053069
Indian Red

rgbIndigo
8519755
Indigo

rgbIvory
15794175
Ivory

rgbKhaki
9234160
Khaki

rgbLavender
16443110
Lavender

rgbLavenderBlush
16118015
Lavender Blush

rgbLawnGreen
64636
Lawn Green

rgbLemonChiffon
13499135
Lemon Chiffon

rgbLightBlue
15128749
Light Blue

rgbLightCoral
8421616
Light Coral

rgbLightCyan
9145088
Light Cyan

rgbLightGoldenrodYellow
13826810
LightGoldenrodYellow

rgbLightGray
13882323
Light Gray

rgbLightGreen
9498256
Light Green

rgbLightGrey
13882323
Light Grey

rgbLightPink
12695295
Light Pink

rgbLightSalmon
8036607
Light Salmon

rgbLightSeaGreen
11186720
Light Sea Green

rgbLightSkyBlue
16436871
Light Sky Blue

rgbLightSlateGray
10061943
Light Slate Gray

rgbLightSteelBlue
14599344
Light Steel Blue

rgbLightYellow
14745599
Light Yellow

rgbLime
65280
Lime

rgbLimeGreen
3329330
Lime Green

rgbLinen
15134970
Linen

rgbMaroon
128
Maroon

rgbMediumAquamarine
11206502
Medium Aquamarine

rgbMediumBlue
13434880
Medium Blue

rgbMediumOrchid
13850042
Medium Orchid

rgbMediumPurple
14381203
Medium Purple

rgbMediumSeaGreen
7451452
Medium Sea Green

rgbMediumSlateBlue
15624315
Medium Slate Blue

rgbMediumSpringGreen
10156544
Medium Spring Green

rgbMediumTurquoise
13422920
Medium Turquoise

rgbMediumVioletRed
8721863
Medium Violet Red

rgbMidnightBlue
7346457
Midnight Blue

rgbMintCream
16449525
Mint Cream

rgbMistyRose
14804223
Misty Rose

rgbMoccasin
11920639
Moccasin

rgbNavajoWhite
11394815
Navajo White

rgbNavy
8388608
Navy

rgbNavyBlue
8388608
Navy Blue

rgbOldLace
15136253
Old Lace

rgbOlive
32896
Olive

rgbOliveDrab
2330219
Olive Drab

rgbOrange
42495
Orange

rgbOrangeRed
17919
Orange Red

rgbOrchid
14053594
Orchid

rgbPaleGoldenrod
7071982
Pale Goldenrod

rgbPaleGreen
10025880
Pale Green

rgbPaleTurquoise
15658671
Pale Turquoise

rgbPaleVioletRed
9662683
Pale Violet Red

rgbPapayaWhip
14020607
Papaya Whip

rgbPeachPuff
12180223
Peach Puff

rgbPeru
4163021
Peru

rgbPink
13353215
Pink

rgbPlum
14524637
Plum

rgbPowderBlue
15130800
Powder Blue

rgbPurple
8388736
Purple

rgbRed
255
Red

rgbRosyBrown
9408444
Rosy Brown

rgbRoyalBlue
14772545
Royal Blue

rgbSalmon
7504122
Salmon

rgbSandyBrown
6333684
Sandy Brown

rgbSeaGreen
5737262
Sea Green

rgbSeashell
15660543
Seashell

rgbSienna
2970272
Sienna

rgbSilver
12632256
Silver

rgbSkyBlue
15453831
Sky Blue

rgbSlateBlue
13458026
Slate Blue

rgbSlateGray
9470064
Slate Gray

rgbSnow
16448255
Snow

rgbSpringGreen
8388352
Spring Green

rgbSteelBlue
11829830
Steel Blue

rgbTan
9221330
Tan

rgbTeal
8421376
Teal

rgbThistle
14204888
Thistle

rgbTomato
4678655
Tomato

rgbTurquoise
13688896
Turquoise

rgbViolet
15631086
Violet

rgbWheat
11788021
Wheat

rgbWhite
16777215
White

rgbWhiteSmoke
16119285
White Smoke

rgbYellow
65535
Yellow

rgbYellowGreen
3329434
Yellow Green


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.