Global conversion of 'fill colour' needed, e.g. find 'fill colourA' ' replace with 'fill colour B'

[DEllis1440]

I had an Excel spreadsheet created in October 2015 which contained a lot of conditional formatting, specifically assigning colours to cells depending on their numerical values. It consisted of nearly 40 worksheets. The colour coding is significant.

There is also a lot of ordinary (i.e. not conditional) colour formatting

Over time the spreadsheet grew to over 3gb and took a long time to open so I created a new workbook, created new sheets within it and copied the data across. I selected the data on each sheet and using copy and paste I copied the data to the new workbook’s sheets. The new workbook is significantly smaller at 1.5gb

Everything worked OK except for the colour assignments. For instance pale blue and green have been transmuted to magenta and orange! Not only do the new data sets look awful but the significance of the colours has been lost.

How can I globally convert the new colours to the desired ones and why did they change in the first place. At a guess there are probably 60,000 cells affected scattered across the worksheets so a global solution is necessary.

The properties of both workbooks give the file type as Office Open XML spreadsheet and I use Microsoft 365 (ex Office 365). I use an iMac.

Version 1 of the original workbook was created in March 2013. It may have been on a PC. The type was Microsoft Excel 97-2004 Workbook

Before and after shots showing how the colours have changed

Thank you

[#2]

@DEllis1440 

The old workbook probably had a custom color palette. You can import it into the new workbook:

• Open the old and new workbooks.
• Activate the new workbook.
• Select File > Options.
• Select Save in the navigation pane on the left.
• Under 'Preserve visual appearance of the workbook', click Colors...
• Click the 'Copy colors from' dropdown.
• Select the old workbook.
• OK your way out.

[#3]

@Hans Vogelaar 

Thanks but I can't find any mention of colour palette, nor do I have File > Options.

This is all I have under File

 I tried the Save option but there were no options like the ones you showed.

I'm using Excel for Mac version 16.54 (Microsoft 365 subscription)

[#4 · Accepted solution]

@DEllis1440 

Try Excel menu > Preferences > Color > Copy Colors From

[#5]

Great! Thank you so much. It's restored the original colour palette.

Note for anyone else needing to do this, it's actually under
Excel menu > Preferences > Compatibility > Copy colours from