Where/How are Conditional Formatting Rules saved (Calc)?

I have been working on a large project (organizing a large amount of data) and would like to bulk enter conditional formatting rules (over 150 rules to start). I searched here a bit (new to the forum so figuring out how advanced a search can be still) and did not see anything on this.

Basically, where and how are conditional format rules saved and is there a way to edit that file??

(As a larger note, if someone knows a bit about organizing existing data with semi-consistent categories please reach out! I will share the project when it is organized enough to be used).

An example of what I want to input is this:
B21:AMJ21 → Format is → EXACT(B21,$A$21)
B22:AMJ22 → Format is → EXACT(B22,$A$22)
B23:AMJ23 → Format is → EXACT(B23,$A$23)
B24:AMJ24 → Format is → EXACT(B24,$A$24)

Because this: B10:AMJ10,B11:B40,F12:F39 → Format is → EXACT(B10,$A$10) Does not work.

Can that help?


No, I have read through that and copying is ify at best when doing it to large areas. My ideal is a text file or code section that I can directly edit and add 100+ rules to instead of filling out the wizard over and over.

… and copying is ify at best when doing it to large areas.

The range is specified in the condition.

Yeah, but when trying to get something like this to work…it starts acting up and not working properly because it compares the wrong cells:

B10:AMJ10,B11:B40,F12:F39 → Format is → EXACT(B10,$A$10)

So I have to make a new rule for each column or row
I will put an example of what I am doing into the op.

Better upload a sample file here. Thank you.

so far I see nothing complicated:
select B21:AMJ24 and choose Formula is → EXACT($B21;$A21)

I want over 100 of these formatting rules. I am asking for better ways than manually inputting them.

so unzip the “content.xml” out of the …ods -file, and hack on it … good luck!!

Probably because the relative addressing of B10 becomes C10 when the format is applied on C10. If you want to always compare to B10 in row 10 then use EXACT($B10,$A$10) so the column is absolute and the row is relative; you can then easily copy the format to ranges and for other rows the row is adjusted.

If that’s not what you wanted then please explain better or best provide a sample document, edit your question and attach.

And from the initial question I’d think he also needs A$10 to allow the row to be flexible.

If you think to modify the xml is the better way: Create a conditional format like formula is A2 + “myUniqeString54321”, save, then unzip the file as @karolus already told you and search myUniqeString54321 in the editor of your choice…

This is my current solution yes, just need to put them into a file.

Karolus: HMMM, I like it. I will give it a shot!

Karolus: That did it! I have access and can edit directly… Now I just need to figure out how to format the whole line to work with CP. The XML file has a weird cell ID system that I have to sort through.

Really??…this kind of working with xml is not exactly easy, therfore my answer was also meant rather ironically.