Calc conditional format font color and size override

stderr · November 16, 2021, 6:58pm

I’m using V7.2.2.2(x64) in windows 10.

I frequently use conditional formatting to alternate row color in calc by defining a condition to use a formula, either isodd(row()) or iseven(row()), selecting a cell range to which the condition should be applied, and defining a new style to be applied. The style is usually based on the style “Default” with the only change being the background color. This works perfectly as expected about half of the time.

The other half of the time, I can’t override the font color if the row has the conditional format applied. Even if I apply the conditional format rule to a cell range and apply the style “Default” to the range, if the condition was applied, the font color can’t be changed. I can directly format the number type (currency, percent, etc), background color, number format, make it bold, italic, underlined, but not the color or font size. If I increase the font size, the row height changes to accommodate the larger size, but the font remains the default size at the bottom of the cell. I also cannot change the font type.

If I hit F11, and attempt to modify the style Default, I can see this on the menu tab “organizer”:

Contains
Liberation Sans + 10 pt + Black + 11 pt + Calibri + 11 pt + Align to bottom + Rotation only within cell

In a newly created document, none of this information appears under the word “Contains”; I cannot see a way to remove this from the font definition once it’s there.

So far, the only way I’ve been able to work around this is to copy the entire sheet contents and paste them unformatted to a new document, define the conditional format and start over. In this case, it fixes the problem, but I have so many spreadsheets to edit this is extremely time consuming.

Is there a setting somewhere I missed? I have read the help files for hours, and scoured the web looking for something I missed, and am now wondering if it’s just a bug, but I haven’t found a bug report on this either. Any suggestions are welcome and much appreciated.

LeroyG · November 16, 2021, 7:30pm

Can you share a file with a few cells showing the issue? Edit your question and use the Upload icon. ~~Also share LibreOffice version and operating system.~~ Thanks.

stderr · November 16, 2021, 7:32pm

Thanks Leroy… ummmm, the first line of the message has the version of LibreOffice & the operating system? I’ll see if I can replicate the problem in a simple file.

LeroyG · November 16, 2021, 7:33pm

Ups! Sorry.

Save a copy of the file, and delete all sensible/unneeded data.

Hrbrgr · November 16, 2021, 7:35pm

Well, I’m not sure I understood your comments correctly.

However, if you are working with styles/cell styles, you should not use direct formatting at all.

Select the cell ranges that are causing you problems and choose Format>Delete Direct Formatting ( Ctrl + M ) from the menu.

See also:

Undoing Direct Formatting for a Document

stderr · November 16, 2021, 7:42pm

I think I figured this out. If the Default style has an explicitly set font and color, when the conditional format is applied it overrides any direct formatting. To get around it, I have to edit the Default style (press F11, right click Default in the list on the right, select modify, and note what’s written under “Contains” on the Organizer tab). There are two buttons at the bottom of the Cell Style window: Reset, and Standard. Reset doesn’t seem to do anything. If I select each of the folder tabs (Organizer, Numbers, Font, Font Effects, etc) and click the Standard button on each one, most of the data loaded into the “Contains” list on the Organizer tab disappears… and the problem with it.
conditional_formatting_example.ods (9.4 KB)

karolus · November 16, 2021, 7:43pm

The 'default’Style should never something ‘contain’ in the organizer-tab.
“repair” the 'default’style by click on the ‘default’-button at the lower|right in every Tab.

stderr · November 16, 2021, 7:44pm

This does not fix the problem. It seems to be related to whether the Default style has an explicitly defined color, and it’s not obvious how to remove this once set. Even if it’s reset to “Automatic” the problem persists. I think I’m on to it though; see comment below.

karolus · November 16, 2021, 7:46pm

Again … the 'default’Style should not have any explizit setting.

stderr · November 16, 2021, 7:46pm

I agree, but I also am not the original document creator, so I’m looking for the right way to undo this.

LeroyG · November 16, 2021, 7:53pm

I find the behavior to be different depending on whether the font size of the style or the conditional format is set first.

Hrbrgr · November 16, 2021, 7:58pm

…so I’m looking for the right way to undo this.

As already indicated by @karolus ( )
If you want to reset the “Default” Cellstyle, right-click it and choose “Modify”.
Click the “Default” button in the Cellstyle dialog.
You must repeat this for each tab (e.g. font, font effects, etc.) that was previously modified.

I could not see any conditional formatting in your provided file (nothing).
The color changes consist exclusively of direct formatting, which, as I mentioned, should be avoided.

stderr · November 16, 2021, 9:55pm

There is no default button. there is a Standard button; is that what you mean?

LeroyG · November 16, 2021, 11:45pm

See menu Format - Conditional - Manage…; shade style applied in odd rows.

Hrbrgr · November 17, 2021, 5:32am

@stderr

there is a Standard button; is that what you mean?

Yes, sorry for the bent English.

@LeroyG

Yes you’re right, I didn’t see it again because I had already tested.

If I see it correctly, the conditional formatting is used to alternate a light green line.
You can do something like this IMHO much easier with an AutoFormat template.