Ask Your Question

If condition --> change background color of cell

asked 2019-02-02 23:09:06 +0100

os gravatar image

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 flag offensive 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.)

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

2 Answers

Sort by » oldest newest most voted

answered 2019-02-02 23:23:33 +0100

Lupp gravatar image

updated 2019-02-02 23:25:07 +0100

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.

edit flag offensive delete link more


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

os gravatar imageos ( 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.

erAck gravatar imageerAck ( 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.

Lupp gravatar imageLupp ( 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.

os gravatar imageos ( 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.
For more complete information, please read the Calc guide.

Lupp gravatar imageLupp ( 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 ;-)

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

answered 2019-02-03 07:06:13 +0100

pierre-yves samyn gravatar image


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

And apply conditional formatting to the cell to be colored: image description

See color.ods


edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2019-02-02 23:09:06 +0100

Seen: 1,078 times

Last updated: Feb 03