Simple Highlight (change background color) of row or a cell in Calc

English
#1

This seems nuts that I even need to ask this, but I am unable to change the background color of a cell or a row of cells. Before writing this, I completely uninstalled anything to do with LibreOffice, and I really mean I did a complete uninstall. Oddly, sudo apt purge libreoffice* turned up nothing. But dpkg -all showed dozens of libreoffice* files. I manually purged each one of them by filename as trying to purge by * didn’t work. I then deleted the .config files and rebooted. I then did a fresh install of flatpack. Version info is detailed below.

I want to highlight cell A1 so that the background color is yellow or orange or anything other than no color. I have attempted to color the cell two ways: 1) select the cell and right click. Then Format Cells → Background → Color → click a color → OK. Nothing changes with the cell. I also tried using the “Background Color” tool in the menu bar. Nothing changes the color of the background. Nothing. It makes no sense to me. All I wanted to do was highlight a row of data so I could easily focus on it, but there apparently is no way to actually do any sort of highlighting.

Here are some interesting facts: I can change the color of a font with no issue. Also, in the Format Cells box, If I click “Font Effects”, I can vividly see the cell formatted with the proper background color in the small area at the bottom of that tool window. I will call it a “preview” area for lack of any better idea of what to call it. Nevertheless, the actual spreadsheet cell has no background color formatting. As a test, I tried the same thing on a different Ubuntu Linux machine, and had no problem with changing the background color of the selected cells as you would expect. There seems to be something specific to this one machine that is causing formatting to be off, and I’m of the opinion that it is some deeply buried configuration setting.

Any ideas?

Version: 7.6.1.2 (X86_64) / LibreOffice Community
Build ID: f5defcebd022c5bc36bbb79be232cb6926d8f674
CPU threads: 12; OS: Linux 6.4; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Flatpak
Calc: threaded

But this version info shouldn’t matter. I had the same problem with the previous version that was installed on the machine. Not sure what version it was.

NEW INFO: In the sample file I just uploaded, cell A1 should have background Dark Orange 1. It doesn’t. HOWEVER, if I click in the formula edit bar, the cell highlights properly. When I click out of the formula edit bar, the background formatting disappears.
sample_file.ods (11.2 KB)

#2

Post an example file, identify the cells with colored backgrounds, so we can see it on our machine. I use Win10+LibO7612.

#3

If it is 153527 – LibreOffice 7.5 Calc: unable to apply formatting to all cells in spreadsheet then workaround is to use styles.

1 Like
#4

I have exactly the same problem. It isn’t bug 153527, it happens when you try to highlight even a single cell. Does no-one have any ideas?

#5

Have you got high contrast turned on in your operating system? If so, the default for LibreOffice is to turn on high contrast for LibreOffice. This has the effect of turning off colour on-screen for that computer, although it will print and export colour as well as being visible on other computers
To disable high contrast mode for LibreOffice, click Tools - Options - LibreOffice - Accessibility and set High contrast to Disabled

Text color changes back to black when moving to another cell in Calc
#6

Yes! That’s it. In Linux Mint’s “Appearance->Style” settings I had selected the High Contrast option, I don’t remember why now, probably it just looked best. Choosing Adwaita instead fixes the Calc problem. Many thanks.

However I have another problem. I can’t change the font colours in individual cells using the Font Colour toolbar button. It’s not that the cells don’t show font colours; they do, where I have set them at some time in the past. But now I can’t change them.

#7

You might have conditional formatting set for those cells.
Click Format > Conditional > Manage. In the dialogue that comes up are any cell ranges shown? Do they cover the cells that you cannot change?
You can edit the parameters to include more/exclude more values, or to change the cell styles. You could remove the formatting too or reduce the range covered by the conditional formatting.

#8

No, I don’t have any conditional formatting set up.

#9

I assume the sheet or cells aren’t Protected, otherwise you would see a dialogue telling you they were protected if you tried to alter the cell colour.

Another possibility is the cell formatting. If you look at currency format, you will see that negative numbers are sometimes formatted in red. This isn’t the only possibility, there are 8 colours listed in Help, Number Format Codes

Selecting the cells and clicking Format > Clear direct formatting (Ctrl+M) will remove formatting colour. The exception is, if you have created a cell style with that formatting, in which case you would need to select the cells and then double-click another cell style such as Default .

#10

No, none of the above worked (but thanks!)

I have now found the answer on another thread:
Why am I not able to change the color of font? - #10 by HardyN)

“The following menu item was checked in my LibreOffice Calc:
View → Value Highlighting
Not sure if I set this at some time in the past or is part of ongoing updates.
Regardless, LibreOffice calc was set to highlight the values; that is to say, manually entered values in the form of numbers (1,2,11,42,etc) are shown blue and calculated values are shown green with common text shown black. I like the feature, so I left it as such, but found that my choice of text font colour would not show. That is, different colours of my choice that I have set for the text would not show up on the screen.
I have unchecked: View → Value Highlighting
and now the font colour changes show up that were manually set in the past.”

Worked for me (Spiro) too!

#11

However, I now find it’s only half fixed. Doing the above fixes it for that session. But when you close Calc, and reopen it again, even for the same spreadsheet, Value Highlighting is back on and you have to turn it off again.
I cannot see a way to keep it turned off by default. And if I can’t, I’ll have to stop using Calc, because it is a total bloody nuisance. Just these two problems have wasted hours of my time.

#12

You are indeed correct, the ‘View → Value Highlighting’ is not maintained when closing the spreadsheet, however, it does matter which extension you are using.
I have just tested the function and find that, if I am using and saving the spreadsheet as a Libreoffice .ods file, then the function is maintained when closing.
If I use and save the file as a Microsoft .xls file, then the function is NOT maintained, odd ?!
I had the idea that each of Libreoffice and Microsoft operate differently with regard to some functions.
I quickly tried the test I mentioned, but I have not extensively tested Libreoffice and Microsoft for any other differences.
I would suggest using the spreadsheet as an .ods and save it as that to maintain the ‘Value Highlighting’.
Unsure if any other functions are affected.

#13

Probably not odd, if the old .xls Excel file format does not support value highlighting.

#14

I almost never save files as .ods format, all my files are .xls. Anyway,
the value-highlighting function was not only maintained between
sessions, it was restored even when I had switched it off in the
previous session. This seems to suggest it is a property of Calc’s
configuration, not of the spreadsheet itself.

I have now found what seems to be a workaround, as follows: First, open
Calc without an input spreadsheet file, e.g. by launching it from the
applications menu. Then go to the View menu and turn off value
highlighting. Close Calc. The next time you open an .xls file, it will
have value highlighting turned off; even if you had it turned on last
time you used that spreadsheet! Or so it seems from my experiments.

Perhaps changing an application’s settings when you don’t actually have
a file open is a standard way of changing the defaults. Perhaps some
people will now tell me that “everybody knows” this, and I should have
known it too. But if one is trying to fix a problem that suddenly
appears without warning, it isn’t obvious that changing the defaults is
the solution. Nor is it obvious how to change them, not on LibreOffice
anyway.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.