[CALC] Conditional formatting overriding background color

[Laniakea]

Hello,
I’d like to have rows alternating background colors (grey/white), so I used Format > AutoFormatStyles to achieve that.
However in the row “Reserve” I have 3 conditional format rules. If the cell contains one of three words it gets colored with the proper color I chose for that word.
“Ja” gets colored green
“Nein” gets colored red
“Nachbestellt” gets colored yellow.

In the rules for the conditional formatting I have made sure to leave the background on “none”.
Yet it still somehow overrides the autoformats grey color.

How do I fix this?

Example file

EDIT: My OS is Ubuntu Linux 64-bit & I’m running Version: 6.2.8.2
Build ID: 1:6.2.8~rc2-0ubuntu0.16.04.1

[balagge]

How did you define the conditional formatting? I cannot reproduce this behavior. On my system the background stays gray.

[Hrbrgr]

To track this behavior, you should also specify your operating system and LibreOffice version.

Edit your question or write a comment. Thank you.

It is also helpful to have a reduced document in which the behavior can be reproduced.

To upload, please also edit your question and use the paperclip icon.

**Don't write an answer**, these are reserved for solutions.

[Laniakea]

Uploaded an example file & added my OS/LibreOffice Version

[LeroyG]

@anon73440385, Your answer is correct on the sample file. Please undelete it.

Don’t know why there are differences in my results on a new file with cells with manually gray background and with AutoFormatStyles.

red-green-yellow.ods, made with LibreOffice 6.4.7.2.

[Zizi64]

Of course the manually adjusted properties (like the cell background color in your sample file) will be overwritten by the style applied by the CF feature.

You need use more conditions and more styles (Red-White, Red-Grey, … and more). The new condition would be the parity of the row number…

[Zizi64]

Here is a similar question:

link to the question

[Laniakea]

While this is a workaround I feel it won’t scale well. Also it’s not flexible should I need to change the color pattern of the alternating rows from grey/white to something else. I would have to create a lot of new conditions with the new backgrounds again.

But thank you for your answer.

[balagge]

In the example file you use 3 styles for conditional formatting: "Utitled1" (for RED), "GREEN" and "YELLOW".
Out of those, GREEN and RED does hide the cell background, if applied, but YELLOW does not, so YELLOW already does what you want. (try on a gray line).

The style definitions in the file “styles.xml” of your spreadsheet look like this:

<style:style style:name="Untitled1" style:family="table-cell" style:parent-style-name="Default">
  <style:table-cell-properties style:diagonal-bl-tr="none" style:diagonal-tl-br="none" fo:background-color="transparent"/>
  <style:text-properties fo:color="#ff0000" fo:font-weight="bold"/>
</style:style>
  <style:style style:name="GREEN" style:family="table-cell" style:parent-style-name="Default">
  <style:table-cell-properties fo:background-color="transparent"/>
  <style:text-properties fo:color="#00a933" fo:font-weight="bold"/>
</style:style>
  <style:style style:name="YELLOW" style:family="table-cell" style:parent-style-name="Default">
  <style:text-properties fo:color="#ffff00" fo:font-weight="bold"/>
</style:style>

Seems like the setting fo:background-color="transparent" is causing the problem. This is also visible on the user interface:

If you remove this setting from the style then it will no longer override the background of the cell.

To remove (English interface language, if your system is different (German) you can find corresponding action, or set the system interface language temporarily to English in Tools/Options/Language Settings/Languages/User interface):

• select View/Styles (or press F11)
• on the styles pane select the style “Untitled1”
• right click, select “Modify”
• (select the “Organizer” tab, confirm you see “Automatic, Transparent” on the “Contains” area)
• select the “Background” tab
• click “Standard” button
• (select the “Organizer” tab, confirm the “Automatic, Transparent” setting is no longer shown
• click “OK”
• repeat for style “GREEN”

Bottom line:

• checking style contents on the “Organizer” helps in a lot of cases
• setting the background explicitly to “None” (probably that is what you did) actually makes things worse in this case. The behavior of that setting is questionable, though: (1) the setting does not visually appear on the “Background” tab, only shown on “Organizer” (2) if “None” is selected, why does it apply a white background if the style is used? Especially since the styles.xml contains a setting of fo:background-color="transparent", i.e. “transparent” is not normally expected to mean “white”
• this could be a bug report actually because of the problems mentioned above

[Laniakea]

Unfortunately clicking on “Standard” and confirming with “OK” doesn’t seem to get rid of the transparent setting. But your explanation seems to be spot on.

EDIT: the style inherits it’s settings from the “Default” style in which the transparent is also present.

Is the a way to change the “Default” styles settings without the user interface?

[balagge]

@Laniakea In the example file you provided, the fo:background-color="transparent" setting is only applied on the Untitled1 and GREEN styles, not on the “Default” style. And the standard setting works for me as described above. OS: Ubuntu 20.04 64bit, LibreOffice 6.4.6.2.

• Did you press “Standard” after selecting the “Background” tab? the Standard button resets only the current tab content, so if you use it when another tab is selected then it will not reset the background setting
• if your original file is different (as you describe the Default style), you can use Standard on the Default style as well