cant reset color map

  • Thread starter Thread starter Roger Smith
  • Start date Start date
R

Roger Smith

I have several sheets that I am working on for someone else, but their color
map is screwed up so I cant set the colors I usually use. I know how to
reset the color map but for these sheets the following action:

Tools | Options | Color (tab) | Reset (button)

does not reset the map, and in fact doesnt seem to have any affect at all.
Please, does anybody have any suggestions on how to force a complete Reset
of the color map back to its default values?

Thanks

Roger
 
With one of the bogus workbooks open.................

Open a workbook with a good color scheme from before all this took place.

Switch to bogus book and Tools>Options>Color....Copy colors from.

I think you will have to do one book at a time.


Gord Dibben MS Excel MVP
 
Thanks much Gord, but sadly this isnt working. Whenever I open the offending
workbook the color maps of all of the currently open and subsequently open
workbooks seem to be overwritten so that they all have the incorrect map.
When the reset color map function doesnt work what else can I try?

Cheers,

Rj
 
Changes to the color palette are a workbook-only setting and should not affect
any other workbooks.

I cannot replicate your problem.

Have never run across this problem but vaguely remember reading a post or posts
in one of these groups about something similar.

Try a google search on the subject like "color palette reset" or something.


Gord
 
Thanks again Gord. This is beginning to sound like a genuine bug. I had
previously googled and had found a number of other posts describing the same
problem on other boards and blogs but no one had posted any solutions. It
appears that the one thing all have in common is that the workbook was
created as output from the embedded Crystal reporting engine. In several
cases the report came from SAP and others it came from Oracle. Both use the
Crystal engine in their reporting software as does my report.

You said you couldnt reproduce this problem so I have loaded the file that
causes this issue to appear onto our website. You can obtain that file at

http://www.illumant.com/shared_docs/rpt_1_0208example.xls

Here is one of the other posts describing this problem:
I have a workbook that has been "influenced" by a SAP interface and now has
a color palette with very Grey colors. There are no more macros attached to
the workbook, nor any other reason for the color pallete to stay the way it
is, but as soon as I open another workbook along with this one, it "infects"
the color palette of all those other workbooks that are open too. What's
even more frustrating is that I can't reset or copy another "new" color
palette to fix this workbook as excel just plain ignores my request.

Any suggestions will be much appreciated.

Roger
 
Not good news Roger.

I downloaded your workbook.

Other open workbooks were not affected by your color palette although I could
see the modified palette in your workbook..

Then went to Tools>Options>Color and hit Reset which reset the colors to
standard palette.

Saved the workbook, closed and re-opened with the colors at standard palette.

You have something going on at your Excel that is not happening at mine.


Gord
 
Thanks ever so much Gord. Your response was very helpful and extremely
appreciated. I was previously focused on the input file and you demonstrated
that part of the problem is in my Excel installation. I dont have time right
now to reinstall excel, but for others who may be experiencing the same
problem you can force a workaround by running a little VB macro containing
something like the following:

Sub colors56()
' there are 57 colors in the preset palette, indexed 0 to 56, of which 1-40
are given in the "standard" palette

' set some standard colors
ActiveWorkbook.Colors(38) = RGB(255, 153, 204) ' Rose
ActiveWorkbook.Colors(40) = RGB(255, 204, 153) ' Tan
ActiveWorkbook.Colors(36) = RGB(255, 255, 153) ' light yellow
ActiveWorkbook.Colors(34) = RGB(204, 255, 255) ' light green
ActiveWorkbook.Colors(37) = RGB(153, 204, 255) ' light turquoise
ActiveWorkbook.Colors(39) = RGB(204, 153, 255) ' pale blue
ActiveWorkbook.Colors(15) = RGB(200, 200, 200) ' about 10% grey
end sub

You can get the index values and their associated standard RGB settings from
the excellent article published by F. David McRitchie at
http://www.mvps.org/dmcritchie/excel/colors.htm

Cheers,

Roger
 
How would one convert this to C#?:

ActiveWorkbook.Colors(38) = RGB(255, 153, 204) ' Rose
ActiveWorkbook.Colors(40) = RGB(255, 204, 153) ' Tan
ActiveWorkbook.Colors(36) = RGB(255, 255, 153) ' light yellow
ActiveWorkbook.Colors(34) = RGB(204, 255, 255) ' light green
ActiveWorkbook.Colors(37) = RGB(153, 204, 255) ' light turquoise
ActiveWorkbook.Colors(39) = RGB(204, 153, 255) ' pale blue
ActiveWorkbook.Colors(15) = RGB(200, 200, 200) ' about 10% grey
 
I'm guessing the number 13 below is for the 13th color in the pallet?
XLApp.ActiveWorkbook.set_Colors(13,RGBColor);
 
I've been having the same issue, or at least similar...
Open a file in XL, pallet is fine (XL standard). Open another file originating from a SAP report, and it has a different pallet (and not a particularly nice one in my opinion). Switch back to 1st file, and that pallet has changed to the SAP originating one. Try to reset the colors on the 1st file (tools - options - color - reset) and nothing happens. Neither does applying the pallet from the 1st file to the 2nd, looks like once you open one of these 'contaminated' files, it overwrites the default pallet, but just for that session of xl, as closing xl and reopenning resets the problem. However, if you save the 1st filr once the pallet has been overwritten, it gains the new (unwanted) pallet for ever more.
I found a work around to this, at least on my xl (ver 2003). Open a file with the default pallet, and modify one of the colors from standard, save it, and then , change that colour back again and save again. Result, you seem tio now have a XL file with the default color pallet saved as part of it, I've saved mine as 'Default Pallet.xls'.
Now if I open a SAP originating file, it does not overwrite my modified file, and I can then use 'Default Pallet.xls' to overwrite the pallet of the SAP download file. Horray... I'm back to consistent colours again. I hope this helps your similar issue.

Andy
 
This took a little hacking to create, but it worked for me. All of the goofy SAP colors were restored to default and I could format my documents how I wanted them. You can add the following procedure to your personal VBA so that it can be run in any "infected" workbook.

Sub ResetToDefaultColors()

ActiveWorkbook.Colors(1) = RGB(0, 0, 0)
ActiveWorkbook.Colors(2) = RGB(255, 255, 255)
ActiveWorkbook.Colors(3) = RGB(255, 0, 0)
ActiveWorkbook.Colors(4) = RGB(0, 255, 0)
ActiveWorkbook.Colors(5) = RGB(0, 0, 255)
ActiveWorkbook.Colors(6) = RGB(255, 255, 0)
ActiveWorkbook.Colors(7) = RGB(255, 0, 255)
ActiveWorkbook.Colors(8) = RGB(0, 255, 255)
ActiveWorkbook.Colors(9) = RGB(128, 0, 0)
ActiveWorkbook.Colors(10) = RGB(0, 128, 0)
ActiveWorkbook.Colors(11) = RGB(0, 0, 128)
ActiveWorkbook.Colors(12) = RGB(128, 128, 0)
ActiveWorkbook.Colors(13) = RGB(128, 0, 128)
ActiveWorkbook.Colors(14) = RGB(0, 128, 128)
ActiveWorkbook.Colors(15) = RGB(192, 192, 192)
ActiveWorkbook.Colors(16) = RGB(128, 128, 128)
ActiveWorkbook.Colors(17) = RGB(153, 153, 255)
ActiveWorkbook.Colors(18) = RGB(153, 51, 102)
ActiveWorkbook.Colors(19) = RGB(255, 255, 204)
ActiveWorkbook.Colors(20) = RGB(204, 255, 255)
ActiveWorkbook.Colors(21) = RGB(102, 0, 102)
ActiveWorkbook.Colors(22) = RGB(255, 128, 128)
ActiveWorkbook.Colors(23) = RGB(0, 102, 204)
ActiveWorkbook.Colors(24) = RGB(204, 204, 255)
ActiveWorkbook.Colors(25) = RGB(0, 0, 128)
ActiveWorkbook.Colors(26) = RGB(255, 0, 255)
ActiveWorkbook.Colors(27) = RGB(255, 255, 0)
ActiveWorkbook.Colors(28) = RGB(0, 255, 255)
ActiveWorkbook.Colors(29) = RGB(128, 0, 128)
ActiveWorkbook.Colors(30) = RGB(128, 0, 0)
ActiveWorkbook.Colors(31) = RGB(0, 128, 128)
ActiveWorkbook.Colors(32) = RGB(0, 0, 255)
ActiveWorkbook.Colors(33) = RGB(0, 204, 255)
ActiveWorkbook.Colors(34) = RGB(204, 255, 255)
ActiveWorkbook.Colors(35) = RGB(204, 255, 204)
ActiveWorkbook.Colors(36) = RGB(255, 255, 153)
ActiveWorkbook.Colors(37) = RGB(153, 204, 255)
ActiveWorkbook.Colors(38) = RGB(255, 153, 204)
ActiveWorkbook.Colors(39) = RGB(204, 153, 255)
ActiveWorkbook.Colors(40) = RGB(255, 204, 153)
ActiveWorkbook.Colors(41) = RGB(51, 102, 255)
ActiveWorkbook.Colors(42) = RGB(51, 204, 204)
ActiveWorkbook.Colors(43) = RGB(153, 204, 0)
ActiveWorkbook.Colors(44) = RGB(255, 204, 0)
ActiveWorkbook.Colors(45) = RGB(255, 153, 0)
ActiveWorkbook.Colors(46) = RGB(255, 102, 0)
ActiveWorkbook.Colors(47) = RGB(102, 102, 153)
ActiveWorkbook.Colors(48) = RGB(150, 150, 150)
ActiveWorkbook.Colors(49) = RGB(0, 51, 102)
ActiveWorkbook.Colors(50) = RGB(51, 153, 102)
ActiveWorkbook.Colors(51) = RGB(0, 51, 0)
ActiveWorkbook.Colors(52) = RGB(51, 51, 0)
ActiveWorkbook.Colors(53) = RGB(153, 51, 0)
ActiveWorkbook.Colors(54) = RGB(153, 51, 102)
ActiveWorkbook.Colors(55) = RGB(51, 51, 153)
ActiveWorkbook.Colors(56) = RGB(51, 51, 51)

End Sub
 
Hi,

Why not used the built-in method?

Activeworkbook.ResetColors

Or manually via Tools > Options > Colors > Reset

Cheers
Andy
 
Back
Top