Office Themes and VBA Color Sorting/Filtering Issues

[zero269]

Hello,

I'm planning on sharing an Excel Workbook with someone - and perhaps more later on - which includes several sorting and filtering macros that rely on colors.

The only issue I can foresee at the moment is they may be using a different Office Theme. Each theme uses a different color scheme which will prevent the sorting and filtering based on the RGB values in the excel VBA code to work properly.

How do people currently ensure their VBA color-based codes work across the various Office Themes… and even a User's Custom Theme?

I was thinking that one way might be to first check if the current Theme is the default Office, and If yes, proceed with sorting/filtering. If not, then store the active Theme into a variable to later use to restore the theme after the sorting/filtering has taken place.

I should note that using the limited Constant Colors (vbBlue, etc.) are just not a viable alternative. They're too dark or too bright for my use.

Have any of you created VBA code that uses RGB color sorting/filtering to work across all Office Themes?

Here's an example of the Office theme I'm currently using, and one called Facet. As you can see, once I switch to Facet, all of the color values change.

Thanks,

 

[offthelip]

Using color as the only way to store information is not a good way to design a system in my opinion. Color is very good for display purposes since humans usually can deal with color visually very well. However there are four reasons that I can think of that make using color as the source of information a serious disadvantage.

The first one is the one you have come across that color can vary dependent on the theme

Secondly for people that are color blind it doesn’t work very well and can cause errors

Thirdly It doesn’t appear at all if you print it on a black and white laser printer

Fourthly and for me the most important EXCEL VBa is very very slow at reading or writing color to a cell, and the way excel deals with color means it is often very difficult to detect what is causing the color of the cell. It might be directly formatted or it might be the result of one or more conditional formats. Logic built on the color of the cell is difficult to test and can be unreliable if the user uses a slightly different color.

So my recommendation is to redesign your workbook before you share it and use extra “helper” columns to contain real data which capture the information you are currently holding in colors.

You can then use conditional formatting to create the colors you need for the display

 

[offthelip]

I have just thought of a 5th reason:
There are no EXCEL worksheet functions that allow you to deal with color, i.e. sum, count, average, etc by color.