Formatting

Recent Colors

When you customize a color and apply it to an object in Microsoft Office, the color is stored as part of the Office file, and presented in a “Recent Colors” category on the various colors dropdowns. Up to ten of these custom colors are displayed, with the most recent at the left of the list and the oldest moving further to the right until a new color pushes it off the list.

The screenshot below shows three custom colors applied to cells B4:B6 of a worksheet, and where they appear in the color dropdown. The pink color was the latest applied, so it is furthest left. It is highlighted with a thin red outline because it is also the fill color of the active cell.

The numerical and alphanumeric codes in columns C and D are the decimal and hexadecimal representations of these colors. They were generated using the following VBA procedure:

Sub OutputColorInfo()
  Dim c As Range
  
  For Each c In Selection.Cells
    c.Offset(, 1).Value = c.Interior.Color
    c.Offset(, 2).Value = WorksheetFunction.Dec2Hex(c.Interior.Color)
  Next
End Sub

Colors are the combination of the three color channels (red, green, and blue), and each channel can have a value between 0 and 255 (or between 0 and FF in hexadecimal notation). The yellow color above has a blue channel value of 66, and red and green channel values of FF, so the code for this yellow is 66FFFF. Note that VBA presents the channels in BGR order, but it’s still called “RGB”.

A customer asked how to get rid of some but not all recent colors used in Excel. This is not too hard to do, but you should remember that any time you format an object with a new custom color, this new color becomes a new Recent Color.

Where are Recent Colors Stored?

Recent Colors appear in the color dropdowns only when the file they were defined for is the active file in its Office application (in this case, the active workbook in Excel). This means they must be stored somewhere within the document.

The first step in modifying the Recent Colors is to close the Office file. Below we see the workbook “recent colors.xlsm” in a Windows Explorer window.

Office files are really zip files which contain xml and other files within a well-defined directory structure. You can access this structure by simply adding the file extension “.zip” to the file name, and dismissing the warning about changing file extensions.

Double click the zip file to see the top-level structure of the file.

Double click on the “xl” directory within the file.

Finally, copy the “styles.xml” file, navigate out of the zip file to the directory the Office file was saved in, and paste the xml file here.

Open “styles.xml” in your favorite text editor. I use Notepad++, which can apply syntax-based color coding to files it recognizes. This is the entire content of my particular styles.xml file. Note the <mruColors> tag near the end of the file. This contains three <color rgb="blahblah"> tags that contain the definitions of our MRU (Most Recently Used) colors.

Changing Recent Colors

Microsoft Office doesn’t bother making the XML easy to see by using line feeds and spaces, but you can go ahead and apply your own, as I’ve done below.

Note that the RGB values in the <color> tags begin with an extra FF (for the transparency channel), and list the red, green, and blue pairs in the RGB order, not the BGR order produced by our VBA routine above. No big deal, Office uses the XML RGB for its user interface and VBA uses BGR, and they don’t talk to each other, so they don’t confuse each other, they only confuse us.

Delete the <color> tag(s) you don’t want (you could also change the color or add new ones if you can determine the desired RGB values). If you delete all of the <color> tags, you must also delete the <mruColors></mruColors> node that remains, or Excel will tell you there’s a problem with the file and will offer to recover as much of it as possible. Leaving an empty <colors></colors> node doesn’t lead to this problem, nor does removing it.

Save the xml file, and copy it back into the “xl” folder in the zip file. Delete the “.zip” extension you added to the Office file earlier, so it is left with its original Office extension (“.xlsm” for our workbook), and reopen the file.

The screenshot below shows the three colored cells, but only the two non-pink Recent Colors in the dropdown. We have successfully removed the recent color from the workbook’s remembered list, but we haven’t removed the color from any objects we formatted with it.

Note that reapplying the pinkish custom color to any object in the workbook will reestablish it as one of our Recent Colors.

Thanks to PowerPoint MVP Steve Rindsberg for showing me the <mruColors> tag I was too busy to notice the first time I looked. Check out Steve’s PowerPoint FAQ Pages and his PPTools – PowerTools for PowerPoint.