Change all background color X to color Y

[swampwiz]

Would like an extension that can change all cells of a certain background color to another
That's it. I have a spreadsheet with over 100K cells, and I'd like to swap a pair of background colors, both of which are custom colors that for whatever reason have not persisted in the color palette.

Edit: Changed subject, was Would like an extension that can change all cells of a certain background color to another It's best to say what you want to do without suggesting how because that often leads to XY Problem. Make your post understandable by others  -- MrProgrammer, forum moderator

[MrProgrammer]

both of which are custom colors that for whatever reason have not persisted in the color palette

Color picker not showing current color
[Solved] I've accidentally modified a pre-defined color! (your own topic)

I have a spreadsheet with over 100K cells, and I'd like to swap a pair of background colors

When you use styles instead of directly setting the cell background color with Format → Cells, then this task is trivial. You just select the two styles and change their background colors. All of the cells which use those styles are immediately changed.

You will continue to struggle with spreadsheets until you learn how to use styles. The best solution is to remove all direct formatting in your entire spreadsheet, then create and apply styles to set the background colors and other formatting attributes like font, alignment, underlining, number format, etc. It is easy to apply a style to a range of cells by selecting them and double-clicking the style in Format → Styles and Formatting. You were advised to use styles in 2016 so this mess is something you created for yourself. It is better to apply formatting as the last step in creating a spreadsheet.

Would like an extension that can change all cells of a certain background color to another.

You could try RGB which I found immediately by searching the extension website for color. But I don't think this will fix the underlying problem that your spreadsheet does not use styles. I have never installed any extensions from that website. If RGB doesn't seem to help for this task you can make the changes to the cell background colors with macros, as explained in these two topics. I would use a new range to get the background color of every cell in the range which needs to be updated. Then I'd use Find&Replace to change the colors there as desired, for example swapping AABBCC with DDEEFF and leaving the other colors unchanged. Finally I'd set the color of every cell in the range which needs to be updated to the new colors. You might need to modify the macro from the second topic a bit for your task. I do not offer to help with that.
Get cell color as RGB value in another cell
Set cell color from RGB value in another cell

If this solved your problem please go to your first post use the Edit ☐ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[swampwiz]

> You will continue to struggle with spreadsheets until you learn how to use styles.

Redditors would say, "you will continue to struggle while you continue to use Apache OpenOffice".

I guess I'm hosed in that there will be no way to easily change every cell that has a certain background color to having a style. And it would probably be easier for me to write my own console app that would do this programmatically.

[RoryOF]

You might investigate editing content.xml with a suitable editor. The OpenOffice file is in fact a .zip file and can be opened with an archive manager, to permit access to its components.

[FJCC]

@swampwiz - There might be a way to recolor your cells easily with a formula or a simple macro. Are the cells colored because they meet some criteria? Is the whole data set confined to a single block without blank rows or merged cells? Please describe the data layout, make an example document with a much small data set and post it here.

[swampwiz]

@swampwiz - There might be a way to recolor your cells easily with a formula or a simple macro. Are the cells colored because they meet some criteria? Is the whole data set confined to a single block without blank rows or merged cells? Please describe the data layout, make an example document with a much small data set and post it here.

No, the colored cells are all over the place, with no other identifying information.

[RoryOF]

With an archive manager, open the OpenOffice file and then open content.xml with a suitable plain text editor. Find all instances of "aabbcc" and replace with "xxyyzz" where "aabbcc" is the six letter/digit code of the unwanted colour and "xxyyzz" is the code for the new replacement colour. Global Find and Replace will probably do the job very swiftly. Then Save content.xml, rezip the archive with this new content.xml, and all should be correct.

Of course, the usual caveats about using a copy file apply, as also ability to use the various tools correctly.

This would take less than five minutes for the entire process.

[Alex1]

I tried RoryOF's solution. A spreadsheet with 100k cells isn't too big for Notepad. Keep in mind that all sheets are processed with global replacing.
Unlike OpenOffice, LibreOffice shows the hexadecimal color values in the cell formatting dialog.

[RoryOF]

Users often overlook the possibility of editing content.xml. This can be dangerous, as an error can (read: probably will) damage the altered file, hence the suggestion to use a copy file until all is well.

[swampwiz]

With an archive manager, open the OpenOffice file and then open content.xml with a suitable plain text editor. Find all instances of "aabbcc" and replace with "xxyyzz" where "aabbcc" is the six letter/digit code of the unwanted colour and "xxyyzz" is the code for the new replacement colour. Global Find and Replace will probably do the job very swiftly. Then Save content.xml, rezip the archive with this new content.xml, and all should be correct.

Of course, the usual caveats about using a copy file apply, as also ability to use the various tools correctly.

This would take less than five minutes for the entire process.

What is an "archive manager"?

[Hagar Delest]

An application like Winzip or 7zip to compress/decompress files.

[swampwiz]

An application like Winzip or 7zip to compress/decompress files.

But wouldn't the file be all mangled because of the compression?

[FJCC]

All OpenOffice files are zip files. You can change the name of a Calc file named Data.ods to Data.zip and then open it with and archive manager. Windows will show a warning when you change the file extension, but it's safe to do. In the zip file you will see a file named Content.xml which contains the data and formatting of your spreadsheet. However, if you are not familiar with editing xml data, you might find the Content.xml file rather imposing. Always make a back up copy of a file before editing its Content.xml file.

[Zizi64]

And it would probably be easier for me to write my own console app that would do this programmatically.

vs:

What is an "archive manager"?
But wouldn't the file be all mangled because of the compression?

I suggest you to use
- the existing features of the office suites (mainly the styles)
- LibreOffice instead of the OpenOffice


All of the XML based documents (the ODF of the Document Foundation and the OOXML of the Microsoft) use compressed (lossless compressed) formats, because they have normal file system stuctures inside: There are Folders and Files inside the "document file".

[swampwiz]

And it would probably be easier for me to write my own console app that would do this programmatically.

vs:

What is an "archive manager"?
But wouldn't the file be all mangled because of the compression?

I suggest you to use
- the existing features of the office suites (mainly the styles)
- LibreOffice instead of the OpenOffice


All of the XML based documents (the ODF of the Document Foundation and the OOXML of the Microsoft) use compressed (lossless compressed) formats, because they have normal file system stuctures inside: There are Folders and Files inside the "document file".

It's far too late to modify all the cels. I have about 24K of them in no particular order.

[Hagar Delest]

Redditors would say, "you will continue to struggle while you continue to use Apache OpenOffice".

In this very case, I doubt any application would natively do better.

it would probably be easier for me to write my own console app that would do this programmatically.

Then go for it, you have all you need. Either the macro way by testing the background color and replacing it when needed or the surgical way by doing that manually find and replace directly in the inner files of the .ods.