Color Palette Conversion Tool for Excel 2007
4

Color Palette Conversion Help Page

Color Palette Conversion Help Page
[email_link] | [print_link]

This is the help page for the Color Palette Conversion Utility for Excel 2007

Open the Color Palette Conversion workbook and enable macros.

The tool has a built-in visual guide to step you through it.  Click the Help button at the top of the worksheet to enable the guide.  Note: Macros must be enabled to access the help and run the tool.

The tool is also compatible with Excel 2010 (not tested in 2013 yet).

Overview

The tool will build a color palette based on the colors used in your workbook, and then update the previous version (56-color) palette with your colors.  You can also choose which colors to use.  The previous version palette is stored in the workbook file, and used to convert colors to the 56-color palette when a 2007 workbook is opened in previous versions of Excel.

This guide references components (tables and drop-down menus) of the tool.  Use the following diagram for reference.

Excel 2007 Color Palette Conversion Diagram

Quick Guide

  1. Open the workbook(s) you want to update.
  2. Open the Color Palette Conversion workbook and enable macros.
  3. Select your workbook name from the Get colors from (open workbooks): drop-down.  Colors used in workbook table will be updated.  Step 1 on visual guide in tool
  4. Copy cells containing colors from the Colors used in workbook or 2007 Theme Color Palette and paste in the Previous Version Color Palette table.  Step 2 on visual guide in tool
  5. Select workbook(s) to update from Update Previous Version Palette drop-down, and click the Update Previous Version Palette button Excel Update Previous Color Palette Button .   Step 3 on visual guide in tool
  6. Check it by going to the updated workbook and go to: Office Button > Excel Options > Save (option in left sidebar) > Colors… button (after choose what colors will be seen in previous versions).

Detailed Guide

The Color Conversion Tool can be used as a stand-alone tool to update other workbooks, which is explained here.  You can also copy the Color Palette worksheet into your workbooks if you want to make frequent updates to the previous version color palette.

The numbers on headings below correlate to the numbers on the quick guide above.

1. Open workbook(s) to be updated

The tool will retrieve colors used in your workbooks to make updating the previous version palette easier.  You will be able to apply the modified previous version palette to multiple workbooks.

2. Open Color Palette Conversion workbook

Macros must be enabled to use this tool.  Learn how to enable macros when opening workbooks.

3. Get colors from open workbooks (optional)

This tool will retrieve all the colors you have used in the font, shading, and borders in your workbook, and fill the Colors used in workbook table with them.  Select your workbook name from the Get colors from (open workbooks): drop-down to fill the table.  The drop-down is a list of all open workbooks on your computer.

This process can take a few minutes if your workbook has a lot of worksheets or used cells (cells containing data).  This step can be skipped if you know what colors you’ve used and want to copy them directly from the 2007 Theme Color Palette table.

4. Copy colors to previous version palette

The Previous Version Color Palette table on the right side currently contains the default colors of the 56-color palette used in previous versions of Excel.  This table will be used to update the previous version palette in your workbook(s).  So you want to replace the colors on this palette with colors you’ve used in your workbook.  Users opening your workbook with previous versions of Excel will be limited to the color palette you create, so it’s best to not replace some common colors like red, green, blue, yellow if users will be making their own updates to the workbook.

The easiest way to replace colors is to copy the cells from the Colors used in workbook or 2007 Theme Color Palette tables on the left, and paste them in the Previous Version Color Palette on the right.

Alternatively, you can use the numbers assigned to each cell on the left side tables.  Type the number from the cells on the left into the Previous Version Color Palette table (right), and click the Transfer Colors button.  The table will be updated with the colors from the left.

The purpose of the numbers is to keep the colors in memory.  If you want to revert to the default 56-color palette to see which colors you replaced, click the Restore Default 2003 Palette button.  The default colors are now in the Previous Version Color Palette table.  But the color numbers remain in the cells.  To bring your colors back, simply click the Transfer Colors button.

5. Update previous version palette

The Update Previous Version Palette (open workbooks): drop-down contains a list of all open workbooks.  Select the workbook you want to update, or select – All Open Workbooks – to apply to all.  Click the Update Previous Version Palette button  Excel Update Previous Color Palette Button (right of drop-down) to update the previous version color palette in the selected workbook(s).

6. Check the palette

Check the palette(s) you’ve just updated by going to the updated workbook and go to: Office Button > Excel Options > Save (option in left sidebar) > Colors… button (after choose what colors will be seen in previous versions).  You should see the 56-color palette with the colors you’ve pasted in to the Previous Version Color Palette table on the Color Conversion Tool.

To restore the palette to the default 56 colors you can click the Reset button in the Colors dialog box you just opened.  Or, you can go to the color conversion tool, click the Restore Default 2003 Palette, then click the Update Previous Version Palette button.

Action Buttons

There are four additional buttons below the Previous Version Color Palette table.

  • Transfer Colors – copies colors from the tables on left to the Previous Version Color Palette table based on the numbers entered in the cells in the Previous Version Color Palette table.  The numbers will remain after restoring default colors (see next item), to easily revert between custom and default colors.
  • Restore Default 2003 Palette – Restores the Previous Version Color Palette table to the default 56-color palette used in 2003 and previous versions of Excel.
  • Clear Assignments – Deletes the number assignments in the Previous Version Color Palette table, but does not change the colors.
  • Retrieve Palette – Retrieves the previous version 56-color palette from the workbook selected in the Update Previous Version Palette drop-down.  The colors are loaded in to the Previous Version Color Palette table.

Copy Color Palette worksheet into your workbook (Optional)

  1. Right click on the Color Palette tab at the bottom of the sheet and select “Move or Copy…” from the menu.
  2. Select your workbook from the To: drop-down.
  3. Check the “Create a copy” box, bottom left of dialog box.
  4. Click OK.
  5. Use the Color Palette Conversion Tool as described above.

Additional Resources

Color Palette Conversion Download Page

Contact us with questions, comments, ideas for improvement, or additional services.

Blog Articles

Excel’s Color Palette Compatiblity Issue & Solution

Click Here to Leave a Comment Below 4 comments
Bruce L - October 18, 2013

Bug report: if any cell contains more than one font style, the Font.Color returned is Null, which causes a runtime error when attempting to assign it to var ‘strColor’.

Apologies if this is a known/published limitation – I’m in a rush so no time to find out!

Reply
    Jon Acampora - October 18, 2013

    Hi Bruce,

    Thank you for reporting the bug. Yes, this is a known issue. I will fix it when time permits. 🙂

    Please let me know if you find any other issues.

    Thanks!

    Reply
      Bruce L - October 19, 2013

      No worries, and thanks for such a handy util. My org is currently running a hellish mix of four different Office versions from 2003 upwards over hundreds of sites and the CPC tool has been a life saver, allowing me to take advantage of the latest tools to create workbooks and still have a zero-effort way of making my work guaranteed presentable no matter who opens it.

      Reply
        Jon Acampora - October 20, 2013

        I was in a similar situation of being in an org running multiple versions back when I created this. I am sorry to hear that and I feel your pain. 🙂

        I am excited to hear the util is working for you. Let me know if you think of any ways to make it faster to speed up your process.

        Reply

Leave a Reply: