Cannot make conditional formatting keep original font

[Garawa]

I have a complex sheet which highlights many cells using MAX and MIN conditional formatting. I am tidying it up a bit but some appear to have altered so I have created new formats before I delete the others. The trouble is, I cannot stop the formatting from changing the font size and colour even though I create a new format which ONLY changes the background. The green fill is set to change the highest cell to a green background but keep the existing font size (7). As you can see, it appears to change the font colour to black and size to 11, the default figures. If I change the colour to default, it has no effect on the colour.

But, when I enter the same formatting into a new test sheet and use smaller text (7 again) and with different colours, the same formatting works correctly and does not alter the cell appearance at all. There are other formats available on the main sheet but are not controlling any of these cells. Why is working correctly on all but my main complicated sheet?

Annotation 2020-08-06 1155.jpg (7.09 KiB) Viewed 901 times

[Garawa]

I've sort of figured it out. It's because it is linked with "Default" and this formatting contains the Black 11 style. However, the new style has to be linked with either another style or default so I need to somehow clear this.

[Zizi64]

The Conditional Formatting (CF) function uses the cell styles. And you can modify the properties (font, size, etc...) of the applied styles as you want it.

[Garawa]

But is over-riding the styles in the cell because everything links back to default which has a Black 11 style font in it. When I open a new sheet, Default has no font styles saved into it therefore does not over-ride the font that sits currently in the cell. I somehow need to delete "Default" (which cannot be done), remove the font styles from inside it or make a style which doesn't link to default. When I make a new style, I must link it to "Default" or to another style I made which is also linked to it anyway.

As parts of the sheet use smaller text than others, I have ended up with numerous formatting styles when only one is really needed. If I select the correct font size, this overrides the fonts in the other parts of the sheet resulting in another style to be created.

[Zizi64]

As parts of the sheet use smaller text than others, I have ended up with numerous formatting styles when only one is really needed. If I select the correct font size, this overrides the fonts in the other parts of the sheet resulting in another style to be created.

Please upload your ODF type sample file here.

[Garawa]

Ok, so here is a heavily trimmed version of my sheet. You can see that, as styles started to go wrong, I have had to create many more each with a slightly different font size or colour. I have left the formatting alone apart from trying to alter the rows under "Shots on Target" on the left so the difference can be seen. The new conditional format is called "Max Fill Green" (there is a red version too) linked to "Default" and I simply chose Green/Yellow 9 as the fill colour with no alterations to the cell's font however this creates enlarged text as "Default" contains a font size of 11. When I create a new spreadsheet, this works as Default has no size or colour requirements and so it leaves the font size and colour alone and only fills in the background colour of the cell.

[Zizi64]

I suggest you to delete all of manual (direct) formatting properties, and use only the styles.

[Garawa]

Any ideas why Default is fine in a new spreadsheet but not the old one though? That doesn't seem to make sense.

[Garawa]

What are the styles you mention? I presume they are the ones shown in F11? If so, these are the ones that there are multiple of that I cannot alter. I even tried importing the example sheet I made to my main sheet so that I can access the correct style but "Default" hijacks it and starts manipulating the text again! Can you explain if there are any styles I am missing if I delete the formats as I cannot find any?

[Garawa]

This is what I mean. This is the box in my main sheet showing the formatting involved with DEFAULT which I need to clear. I cannot remove the font or its size. All I can do is change it to another which is no good when the style is used on different font sizes.

This is the formatting involved when I open a new sheet (strangely these formats do not affect whichever font or size I have used in my new test sheet). This only changes the background colour and nothing else which is exactly what I want.

I can neither copy this new format to my old sheet (linked to default, it changes the text again) nor reproduce it in that sheet. Clicking reset on the DEFAULT format does nothing. I have also tried copying my old sheets to the new one I created so I can access the new format but this breaks too many formulas. There has to be a way to achieve this surely as I cannot find it in styles.

[Villeroy]

Whatever you try to do with that sheet, this bird will never fly with or without conditional formattiing because of the way how you organize and arrange data.
Anyway... c.f. is one of the more complex spreadsheet featues. What you seem to stumble upon in this particular case is a limitation in Calc. If you want to combine 3 different fonts, 2 font colors and 4 background colors, you have to define 3*2*4 different cell styles. With different borders for upper, lower and left and right side that gives 96 different styles. No, it's 384 because of the extra borders of the 4 corners.
In Excel a c.f. can say: "Whatever this cell's formatting includes, add a red font color if some condition is matched". You can format the cells manually (or through cell styles) and c.f. is just an overlay on top of this. Contrary to that, Calc is much simpler because it applies all of a style's attributes to a cell.

[Garawa]

Ahh I see. Although combining different fonts and sizes is precisely what I am trying to avoid. The fact I can replicate it perfectly to fill a cell as green or red for maximum or minimum values in a range on a new sheet suggested this should be possible on the old sheet. The workaround of importing my existing sheets into the new sheet works. I can select the new formatting style and it doesn't mess around with the text but it would take ages to fix all the broken formulas. If the limitation was with Calc or my sheet, surely this workaround would fail?