8

We have a 3rd party tool that creates an Excel file (xls, 97/2003 format) and formats some cells in different colours. When I copy a range of cells into a newly created file (xlsx, 2007/2010 format) the colours of those formatted cells change completely - light yellow becomes depp purple etc. The same happens when I move/copy a whole worksheet into a new workbook.

The company that created the 3rd party tool can not be reached any longer (probably out of business), so I am stuck with fixing this behavious in Excel.

Why is this happening, and what can I do to prevent or work around it?

Improve this question

5 Answers 5

Reset to default
12

Right click - Paste Special - All using source theme

This should keep the source theme intact. If that does not work, can you post an example of the source document?

Improve this answer
1
  • any idea how to achieve this in VBA?
    – Richard100
    Jan 14, 2013 at 18:05
2

My apologies for reopening this post. I have done some troubleshooting with this, and my findings are as follows.

Let's say that we are using the "Paste Special - All using source theme" option, only your data and formatting from the original worksheet would be retained, floating objects would not be copied over. This option will only work when there are no floating objects (charts, diagrams, shapes) in that worksheet. VBA:

Cells.Copy
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
    , SkipBlanks:=False, Transpose:=False

To have all contents pertaining to a sheet (including floating objects) one would have to move/copy the sheet to the new/destination workbook. Upon doing this, all colours would change to a different theme, including the colours of charts. This is the case even when colour pallets of both workbooks are the same.

I have attached a file for you to play with. Try copying/moving the sheet to a new workbook and see what happens, this file originates from an Office 2010 platform. I am using Office 365 on Win8, and these standard colours change to different shades of yellow and grey.

This problem is not present when you are using workbooks created from scratch in Office 365, but on files created with previous versions of office, the issue is unresolved when used a later version of Office.

THE SOLUTION: Page Layout ---> Colours ---> Office 2007-2010

And in VBA:

ActiveWorkbook.Theme.ThemeColorScheme.Load ( _
    "C:\Program Files\Microsoft Office 15\Root\Document Themes 15\Theme Colors\Office 2007 - 2010.xml" _
    )
Improve this answer
1

I use Excel 2010 and I had the same issue when copy-paste a sheet of two different files.

I have found following solution:

  1. open the new file where you have pasted the copied cells
  2. right click on the Microsoft icon in the upper left corner
  3. click on the first line "customise rapid access..." (or maybe in the English version it is called "options")
  4. go to the "save" option title and click on it
  5. at the end of the window click on the bar "colors" (under the section sheet visual option)
  6. A new windows with the color palette
Improve this answer
0

Please select the cells which has changed the colors and click on the clear rule. ( You will get Clear rule on conditional formating on home tab.

Improve this answer
2
  • Can you explain how this restores the original color?
    – fixer1234
    Mar 30, 2015 at 6:17
  • Actually while we coping files it seems like duplicate value. Thatswhy it's color changed.
    – Rajmohan Thoyakkat
    Mar 31, 2015 at 6:44
0

Excel 2013 (& maybe before). Page Layout -> Themes -> Colors. If your original sheets were built using one color template and your new sheet is built using a different color template, the colors will change when transferred. Open your new sheet and cursor over the Colors templates until you find the match.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .