# Excel highlight color changes between sessions



## Merovign (Oct 24, 2007)

Excel 2003 SP3

User highlights certain cells in a spreadsheet, saves, exits, comes back to the same file later and, when they open it, the highlight color is different, and a dark enough color to be difficult to read. Start with light yellow or green, end up with dark purple or blue. 

Haven't seen it enough times to be sure if there's a pattern to the color changes, but I have seen it happen a couple of times.

The file is on a shared drive, but tracking doesn't show any changes and no one else is known to update these files.

It's a PC on a domain, I did run a detect and repair but haven't run a full reinstall (yet). Otherwise no problems, no other users are reporting this.

I did check for rules on the cells, and obvious things like color settings in Windows (like high-contrast schemes - but everything else is fine).

I don't even know where to begin looking on this one. 

Has anyone even seen spontaneous highlight changes between sessions?

Thanks,

- Chad


----------



## Merovign (Oct 24, 2007)

Okay found the problem, need help on a fix.

It's the way that Excel (mis)handles palettes. If a custom palette is loaded with a workbook, it overrides the default palette in other files already or subsequently loaded.

In other words, if you set highlight colors in a file, then load a file with a customized palette, the display colors in your first one will change. If you load a new file with the default palette, the custom one still take precedence.

I'm looking for a way to override palette changes to avoid having to manually check and change the palette in each of the many files the user accesses on the network. I did a couple of searches but I'm not coming up with anything.

Ideally, I'd make the default palette in Excel unchangeable, so it would ignore per-workbook settings. The "Reset" button in the color tab of the options dialog appears to only reset changes from the current session, so that's no use, and it's a manual file-by-file fix in either case.

Thanks,

- Chad


----------



## septhemis (Oct 3, 2008)

Hello Chad,

I have been having exactly the same problem and have googled and read quite a few excel forums, but haven't found anything useful. I was wondering if you found a fix or workaround.

Thanks


----------



## Merovign (Oct 24, 2007)

Actually, I did.

Excel has a "misfeature" regarding palettes. I haven't quite discovered the precedence order, but typically if you look at the palette for a file (format - cells - patterns ) for the default palette (open without opening a file) and then load the affected file, you'll see that the palette is different.

Can you force a default palette? No.

BUT, if you save a blank file with the default palette, and open that file after you open one of the affected files, the affected file's palette changes to match the one you just opened (this is the misfeature). If it doesn't seem to work, try opening the default file first.

When you save a file, the current palette is saved, which means that you can "fix" your rogue files in this way by saving them after you load the "default" file.

Let me know if this isn't clear, I'll try to write up a step-by-step solution. I'm about to leave for the weekend, no time now.


----------



## ZVI. (Oct 7, 2008)

Hi,

It is known that some accounting software creates Excel reports with non-standard colors schema. After opening of such report the Excel applies its color palette as default for other open workbooks. 
And as Chad mentioned above, the resetting of color palettes by Excel button does not work in this case.

For solving of color palette issue I have made XLA Add-In (it’s free  ZVI_TrueColors.zip which auto sets the color palette of each activated workbook to standard one.
Hope that this Add-In will help you.

Linked archive (171KB) includes following files:
1. Readme.txt – short description.
2. ZVI_TrueColors.xla – the Add-In which can be installed by menu: Tools / Macros / Browse.
3. Instal.exe – simple (alternative) installation tool for copying of Add-In to XLSTART folder.
4. Uninstal.exe – simple un-installation tool for deleting of Add-In from XLSTART folder.

Regards,
Vlad


----------



## septhemis (Oct 3, 2008)

Chad, your reply makes complete sense. I am trying to design a workbook so that it is independent of other workbooks. Even if other workboks are open with different color palettes, I was looking for something that would override those other workbook palettes and continue opening mine with the standard Excel palette. Thanks for helping me understand the issue from a different perspective though. :smile:

Vlad, thank you for getting to the root of this problem and designing a free add-in. It works exactly as intended. Would it be possible for you to tweak your code so that it works silently in the background with a workbook event such as Workbook_Open() or Workbook_Activate? :sayyes:

Thanks in advance.


----------



## ZVI. (Oct 7, 2008)

Hi Septhemis, 

Thanks for response!

The Add-In is already working silently in the background with catching of events WorkbookOpen() & SheetActivate() for all workbooks at opening as well as for all sheets at activating.

Surely, it is assumed that Application.EnableEvents is not set to False (True is default for Excel). 

In some cases processing of WorkbookOpen() event was not enough, therefore catching of SheetActivate() event has been added.

Please let me know if you have any problem with Add-In.

Regards,
Vlad


----------



## Merovign (Oct 24, 2007)

ZVI, you rock!

Thanks so much, I'll check this out ASAP.


----------



## LMTech (Sep 24, 2010)

Hey Vlad,

I downloaded your add-in and sent it to a colleague, and the file crashed her system. What's going on?

Lisa


----------



## ZVI. (Oct 7, 2008)

LMTech said:


> Hey Vlad,
> 
> I downloaded your add-in and sent it to a colleague, and the file crashed her system. What's going on?
> 
> Lisa


Hi Lisa,

Thank you for the feedback! 

The statistic of the Add-In archive in the given link looks as follows:
Date uploaded:	Oct 6, 2008 6:09:30 PM 
Times downloaded:	362 
Last downloaded:	Sep 23, 2010 9:05:56 PM
Size of archive:	174250 Bytes
There were no complaints on it, just "thanks"  in feedbacks, but may be there is a time for its updating :smile:

Today I’ve downloaded the Add-In from the link for testing and found that it works correctly.

Not sure about the source of your colleague issue. 
But the possible problem can came in VISTA or Windows-7 Operating Systems (OS) if your colleague tried to install the Add-Ins by the aid of INSTAL.EXE because the running as administrator is required in such OS.

Please take into account that INSTAL.EXE is not for 64-bit Operating System.
And even in 32-bit OS it’s just for (readme.txt citation) copying of add-ins into Excel start folder: C:\Program Files\Microsoft Office\OFFICExx\XLSTART\
where xx = 9 for Excel 2000, xx=10 for Excel 2002(XP), xx=11 for Excel 2003, xx=12 for Excel 2007

So, I would recommend the typical installation of ZVI_TrueColors.xla using Excel2003 menu: Service / Tools / Add-Ins / Browse.
For Excel2007 the same is in: Office button / Excel options / Add-Ins / Go / Browse

Your colleague can contact with me via e-mail which is in README.TXT file of the Add-In archive.

Hope this will help,
Regards,
Vlad


----------



## prateeque (Dec 1, 2010)

Hi Vlad,

If i can get your add in to work you will be a real life saver. Unfortunatley i think i'm missing something. I downloaded and extracted your .zip file and installed the add in. I can see the add in "True Colours for Excel" in the list of add-ins. However it does not seem to be working. If i open a new workbook fill it with some of the default colours, then take an excel extract from a software at work, the "default" colours in my original file change. Also if i open an already affected workbook the pallete doesn't revert back to the original. 

What am i doing wrong?
I use excel 2003 on win xp.
Thanks


----------



## ZVI. (Oct 7, 2008)

Hi Prateeque,

To find possible reasons of your issue let me explain some nuances.

If accounting software exports workbook with old color palette table similar to Excel 95 one, then there can be two cases at loading of that workbook into the modern Excel versions.

Case 1. 
a) Excel color palette does not change in open workbooks, but the color indexes of exported workbook don’t fit the visible colors. That is, visually the colors of exported workbook cells are the same as in Account software, but the color indexes still refers to the old (Excel 95) color palette table. Trying to copy of the colored cells from exported workbook to the Excel 2003 (and upper versions) workbook will change color of destination cells according to the Excel 2003 color index. Therefore the colors of the destination cells can be different vs the source cells colors.

b) After cells copying there can be also inverse behavior then all colors of Excel 2003 workbooks are visually converted to Excel 95 palette table without changing of its previous color indexes.

Both situations mean discrepancy of visual colors and their indexes, and colors also can change visually.

Case 2. 
After opening of accounting software’s workbook in Excel the colors of open workbooks convert similar to the Case 1 point b.

Note 1.
The resident AddIn applies an Excel 2003 color palette table to any activated workbook including one exported from Account software.
But please pay your attention on the fact that AddIn does not work if some macros disable events by the aid of Application.EnableEvents = False.

Note 2.
The colors of accounting software’s workbook can be changed relative to its previous visual state because some colors with the same index in old and the applied new palettes are different.

Attached picture reflects the color relationships and recommendation on color choosing.

If this info will not help you please send me the problematic workbook without sensitive data in it. Cells in the example can be even empty but colored.

Regards,
Vlad


----------



## prateeque (Dec 1, 2010)

Thank you for that detailed reply. This whole things makes a lot more sense now. 

Ok so what is happening now is that when i first open a workbook the colours still change but the pallete remains the same so i can change them back to normal. If i re-open that workbook the colours stay. So it is just a matter of me fixing a few workbooks.

Quick question - Everytime i open a workbook and close it without making any changes i still get the message saying "Do you want to save Changes" - Is this a result of the add-in? Some pallete change?

Thanks again.


----------



## prateeque (Dec 1, 2010)

Sorry didn't understand how this tool works.

Works perfectly. Vlad = Genius.


----------

