Cannot make conditional formatting keep original font

Discuss the spreadsheet application

Cannot make conditional formatting keep original font

Postby Garawa » Thu Aug 06, 2020 1:05 pm

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.
Annotation 2020-08-06 1154.jpg


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
Annotation 2020-08-06 1155.jpg (7.09 KiB) Viewed 901 times
Garawa
 
Posts: 100
Joined: Wed Feb 27, 2008 12:28 pm

Re: Cannot make conditional formatting keep original font

Postby Garawa » Thu Aug 06, 2020 1:25 pm

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.
Garawa
 
Posts: 100
Joined: Wed Feb 27, 2008 12:28 pm

Re: Cannot make conditional formatting keep original font

Postby Zizi64 » Thu Aug 06, 2020 1:27 pm

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9724
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Cannot make conditional formatting keep original font

Postby Garawa » Thu Aug 06, 2020 1:48 pm

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.
Garawa
 
Posts: 100
Joined: Wed Feb 27, 2008 12:28 pm

Re: Cannot make conditional formatting keep original font

Postby Zizi64 » Thu Aug 06, 2020 2:07 pm

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.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9724
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Cannot make conditional formatting keep original font

Postby Garawa » Thu Aug 06, 2020 2:43 pm

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.
Attachments
Gills Stats 19-20.ods
(33.61 KiB) Downloaded 42 times
Garawa
 
Posts: 100
Joined: Wed Feb 27, 2008 12:28 pm

Re: Cannot make conditional formatting keep original font

Postby Zizi64 » Thu Aug 06, 2020 2:57 pm

I suggest you to delete all of manual (direct) formatting properties, and use only the styles.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9724
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Cannot make conditional formatting keep original font

Postby Garawa » Thu Aug 06, 2020 4:19 pm

Any ideas why Default is fine in a new spreadsheet but not the old one though? That doesn't seem to make sense.
Garawa
 
Posts: 100
Joined: Wed Feb 27, 2008 12:28 pm

Re: Cannot make conditional formatting keep original font

Postby Garawa » Fri Aug 07, 2020 3:58 pm

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
 
Posts: 100
Joined: Wed Feb 27, 2008 12:28 pm

Re: Cannot make conditional formatting keep original font

Postby Garawa » Fri Aug 07, 2020 4:35 pm

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.
Annotation 2020-08-07 152715.jpg


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.
Annotation 2020-08-07 152716.jpg


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.
Garawa
 
Posts: 100
Joined: Wed Feb 27, 2008 12:28 pm

Re: Cannot make conditional formatting keep original font

Postby Villeroy » Fri Aug 07, 2020 5:02 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 29279
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Cannot make conditional formatting keep original font

Postby Garawa » Fri Aug 07, 2020 5:18 pm

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?
Garawa
 
Posts: 100
Joined: Wed Feb 27, 2008 12:28 pm


Return to Calc

Who is online

Users browsing this forum: Prometheus76 and 20 guests