More on color palettes today…

One of the problems with the previously discussed Excel color tip is that custom colors are not particularly portable. In other words, I can easily set up the custom palette on my computer but sharing that with you is a rigamorole. Instead of the color definitions being embedded in the Excel file, I’d have to give you a template file that you could import the palette from (yech!) every time you want to look at the report with my carefully chosen and designed colors.

This led me to the question of, can I store the color definitions in VBA? The answer is, no surprise, YES! I found the following snippet in a thread over on Mr. Excel.

Private Sub Workbook_Open()
Application.ScreenUpdating = False

ActiveWorkbook.Colors(35) = RGB(0, 115, 106) ‘ Teal
ActiveWorkbook.Colors(36) = RGB(255, 255, 153) ‘ Yellow
ActiveWorkbook.Colors(37) = RGB(52, 99, 175) ‘ Light Blue
ActiveWorkbook.Colors(38) = RGB(244, 154, 193) ‘ Pink
ActiveWorkbook.Colors(40) = RGB(255, 204, 153) ‘ Tan

Application.ScreenUpdating = True
End Sub

Yeah! Now my custom colors will load whenever the workbook is opened. AWESOME. Uh Oh, wait, Excel refers to each of the fifty-six colors by an index number and by the way, the index number has absolutely nothing to do with the color’s position in the palette grid (available from the format menu).

Mr. Excel to the rescue again. In this thread, I found a cool little function that lists our colors 1-56 in color coordinated cells.

Cool Little Function: (oops I had the wrong function in there orginally - sometimes I’m my worst editor)

Sub colors()
For i = 1 To 56
With Cells(i, “A”)
.Interior.ColorIndex = i
.Value = i
.HorizontalAlignment = xlCenter
.Font.Color = vbWhite
.Font.Bold = True
End With
Next i
End Sub

The only missing piece is the original RGB value. So, with a little work in Photoshop, I created the following matrix:

The number in the top right corner of each color block represents the Index number that Excel assigns to that color (see what I mean about it not really being arranged in a particularly useful way? It looks like they messed up a couple times and put some of the numbers in order).

In the lower left hand (ish) corner of each block is its RGB value.

The first five rows represent Excel’s ’standard color palette’ while the sixth and seventh rows represent the colors for chart lines and fills respectively.

Whew! Now I have a reference map for building out that first macro (I know which colors are which in the Excel palette when I change a particular index’s RGB value) and I have that Macro for making the custom color choices portable.

Are there other better solutions for making custom color palettes (in Excel) portable? Let us know how you’ve gotten around this issue

By the way, I am well aware that this tip is another rehash of information found elsewhere - but why should I have to go through another 2 or 3 hours of searching pain if I need this again and more importantly why should you?

Ok, so it’s more like what I’ve been keeping my eye on and this is a desperate attempt to make sure that I actually write something this week, but still here are the things that have held my interest.

• Manoj Jasra over at Web Analytics World had four guest posters this week, Avinash Kaushik, Jennifer Laycock, Gord Hotchkiss and Rand Fishkin. They all cover different topics and each one is worth the read.
• Robbin Steif has an excellent post on what web analysts do all day at her Lunametrics blog, so if your bosses, peers, family, or friends have a difficult time understanding what you do - point them to Robbin.
• Both Stephen Few and the guys over at Juice Analytics pointed out this new tool for creating sparklines and bullet charts (among other things) in Excel, so I’ve been playing with it. By the way, it’s called Microcharts by BonaVista Systems
• Of course, the Hewlett Packard BoD fiasco has been absolutely fascinating (and horrifying) - Scoble is keeping tabs on it: Day 1, Day 2, Day 3, Day 4
• Mom’s back to blogging, this time from Egypt where they’re on a 2 year teaching stint - check out the new masthead for the blog which is the view from her classroom!