We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

[CALC] Conditional formatting overriding background color [closed]

asked 2021-01-04 13:53:43 +0200

Laniakea gravatar image

updated 2021-05-20 08:32:17 +0200

Alex Kemp gravatar image

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. image description

How do I fix this?

Example file

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2021-05-20 08:32:36.887516



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

balagge gravatar imagebalagge ( 2021-01-04 16:40:26 +0200 )edit

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.

ebot gravatar imageebot ( 2021-01-04 21:14:40 +0200 )edit

Uploaded an example file & added my OS/LibreOffice Version

Laniakea gravatar imageLaniakea ( 2021-01-05 08:45:13 +0200 )edit

@Opaque, 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.

C:\fakepath\red-green-yellow.ods, made with LibreOffice

LeroyG gravatar imageLeroyG ( 2021-01-05 14:14:47 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2021-01-05 10:56:10 +0200

balagge gravatar image

updated 2021-01-05 11:00:56 +0200

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: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:name="YELLOW" style:family="table-cell" style:parent-style-name="Default">
  <style:text-properties fo:color="#ffff00" fo:font-weight="bold"/>

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

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
edit flag offensive delete link more


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?

Laniakea gravatar imageLaniakea ( 2021-01-05 13:51:41 +0200 )edit

@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

  • 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
balagge gravatar imagebalagge ( 2021-01-11 14:06:40 +0200 )edit

answered 2021-01-05 10:43:11 +0200

Zizi64 gravatar image

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...

edit flag offensive delete link more



Here is a similar question:

link to the question

Zizi64 gravatar imageZizi64 ( 2021-01-05 11:03:33 +0200 )edit

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.

Laniakea gravatar imageLaniakea ( 2021-01-05 13:54:30 +0200 )edit

Question Tools

1 follower


Asked: 2021-01-04 13:53:43 +0200

Seen: 311 times

Last updated: Jan 05