Loose Color When sheet is copied/moved to another workbook

[kprit]

BACKGROUND

We have an excel file having 4 sheets. This workbook has a few macros (VBA procedures/functions). These sheets gets filled up dynamatically when it is accessed by user over web with there own parameter values.

HOW DOES IT WORK?

User passes parameter values to an ASP file(like client_id,year etc.) which gets passed to the excel macro and this macro then call another macros and fills all the sheets with data. After that user gets a dialog to run this excel from the server or he can download the resultant excel on his machine.

PROBLEM

We don't want to have macros embedded to the sheet which gets download. For this what we do is, we copy/move the entire sheet(after it gets filled up with data) to another sheet(in other excel file) and this file gets download for the user. Now what happen is becoz of this move/copy we lost some background color of cell. We have RGB(214, 227, 247) in our sheet but after move it becomes some other color. This happen even if you do it in excel itself. Right lick on the Sheet tab below and say "Move or Copy..." and select "(new book)" from the combo "To Book" and click OK....

 

[TonyJollans]

Hi kprit,

Ah, colours in Excel, what fun - a bit like the colour-by-numbers paintbooks I used to have as a child.

[ul][li]Each workbook has a colour palette of just 56 colours. You can set each of these (palette) colours to any (RGB) colour you like.[/li][li]Anything within your workbook which is coloured is given a ColorIndex number which is in the range 1 to 56 and refers to the palette.[/li][li]If you set a color to, as in your example, RGB(214,227,247) and it is not in the colour palette the colorindex (and thus the colour) will be set to the closest colour which IS in the palette at the time.[/li][li]If you change a colour in the palette, anything which is coloured with that particular palette colour will change to the new colour which now occupies the position in the palette.[/li][/ul]Now, if you copy coloured content from one workbook to another what you are copying is the ColorIndex references into the palette. If the two workbooks have different colour palettes, they will be coloured diferently. What you must do is also copy the colour palettes. To do this, before or after you copy your sheet, code something like ..

Workbook("Destination").Colors = Workbook("Source").Colors

Enjoy,
Tony