# If condition --> change background color of cell

Is it possible to change the background color using a command? If so, how would it look like with a combined if statement?

=IF(A1>5;100;"change background color of this cell")

edit retag close merge delete

To guide other users to valid answers you may consider to "accept" one of the answers as fully satisfying by clicking on the gray checkmark left of it (which will turn green then).
(It's a bit difficult in this case because it depends on minor aspects which one of the answers is preferrable. Roughly: What I suggested will actually assign the cellstyle to the cell. CF - Conditional Formatting - overlays the explicitly set attributes of the used style to those already set for cells inside the viewed area only. Both proceedings can have advantages and disadvantages depending on users' intentions.)

( 2019-02-03 12:28:00 +0100 )edit

Sort by » oldest newest most voted

The else-part should surely also return a value.
You cannot change the colour attribute directly, but you can assign a named cell style to the calculating cell using the STYLE() function. Assuming the CellStyle named "csMyColorAndSomething" has the wanted attributes set, you can use =IF(A1>5;100+STYLE("Default"); "A1 is too small" & T(STYLE("csMyColorAndSomething"))). STYLE() is made for the side-effect. It always returns the numeric result 0 (zero). Applying the T() function to its tresult thus returns the empty string.

more

@Lupp : Thanks! Seems to be what I am looking for. Works perfectly, even for text comparison!

( 2019-02-03 10:02:21 +0100 )edit

Just as a side note, the STYLE() function is a LibreOffice-only function, whereas conditional formatting is supported by other spreadsheet applications as well.

Apart from that, conditional formatting is preferable as it is cleaner separated from the formula calculation steps in which the STYLE() formatting is executed. Also, STYLE() replaces any other style that may have been applied earlier to the cell, whereas conditional formatting only overlays its style and thus the attributes that are defined differently from the underlying cell style.

( 2019-02-03 14:46:13 +0100 )edit

Thanks for the hint. Portability is one of the "maybe-problems".
I would like to add, however, that the STYLE() function is common heritage of LibreOffice and Apache OpenOffice. In fact it was already a (useful) feature in StarOffice (by StarDivision) in the late 1990es. I strongly hope it will not be sacrificed for the sake of Excel-likness.

( 2019-02-03 15:04:34 +0100 )edit

@erAck : Well, I went for the STYLE function, because it is command line based. If you could tell me, how to use conditional formatting, I would be very grateful.

( 2019-02-03 15:36:37 +0100 )edit

@pierre-yves-samin already showed you the dialog to use.
In addition you need to understand that the entry Error there must be the name of a cell style having set the attributes (reg background is shown) you want to be overlaid.

( 2019-02-03 19:53:21 +0100 )edit

the STYLE() function is common heritage

I know, and it pre-dates conditional formatting. It was introduced for asynchronous Add-In functions back then. Nowadays conditional formatting is the better approach. But STYLE() also won't be sacrificed ;-)

( 2019-02-04 22:04:57 +0100 )edit

Hi

You can also use simply: =IF(A1>5;100;"")

And apply conditional formatting to the cell to be colored:

See color.ods

Regards

more