[Calc] How to set background color from cell format definition?

[ajlittoz]

I’d like to set background color from cell value. Foreground color is easy to set from cell format. E.g., to have blue text for positive values and red text for negative value while keeping default color for zeroes, one can define this format;

[Blue]0;[Red]-0;0

Setting background color is harder.

1. A macro can be defined, but it is rather overkill for such a simple need and you must not forget to rerun the macro when something changes in the sheet.
2. Conditional formatting may also be attached to the cell.
   However, this means styles must also be defined for every distinct case.
   Maybe overkill again for this simple case.

Is there an undocumented feature in cell format definition to do this?

Alternatively, would it be a desirable feature to add to the wish list?

PS: I tried to create “vector” conditional formatting by selecting a range of cells and defining a formula to set style from a corresponding range of ‘source’ cells but apparently the “vector” semantics is not understood.

[mariosv]

Hi @ajlittoz, please what is your LibreOffice version?, and can you detail what you want to do/get?

[ajlittoz]

My question is facility inquiry. I know conditional formatting is available in LO >= 3.5 or 3.6.

My goal is to highlight cells (usually background color, but other visual attributes might be useful) based on values, either in the cell itself or some other cell.

As an application example, think of a “To Do” sheet with deadline dates. When time is running short, tasks needing attention would have their cells or line highlighted yellow, while overdue tasks would be red.

[oweng]

Set background colour from “cell format” or “cell value”? The provided example [Blue]0;[Red]-0;0 indicates number format codes (help page). These can only be used to format the number, not the cell itself. Using a cell value to format a cell is provided via the conditional formatting facility. I know of no other way to set a background colour, other than a macro (UNO API) as you indicate in the question.

There are a few enhancement requests related to conditional formatting that may be worth reading through to get a sense of how others describe how they would like to see this facility expanded / altered:

• fdo#59317, Conditional formatting: Use better text styles by default.
• fdo#59413, UI: Allow Styles only used in CONDITIONAL FORMATTING for ‘Find & Replace - Styles’.
• fdo#59415, UI: Allow ‘Find & Replace - Styles’ for Styles used as CONDITIONAL FORMATTING.
• fdo#61313, CONDITIONAL FORMATTING: Icon set should have a color reverse switch.

Presumably your question relates to the Condition and Date conditional formatting methods, both of which appear to use cell styles. It may be worth raising an enhancement request to suggest a simple colour picker type of styling alternative, as used in the Color Scale and Data Bar methods.

Note: It is evident from bug fdo#75826 that using the STYLE() function in combination with conditional formatting is a costly combination at present.

[ajlittoz]

Please, excuse me. My wording was effectively erroneous.

You pointed out my goal: trying to use the number format to change the cell background color. I understand from your answer that number format can change the number only not its environment, such as the cell it resides in. Again from your answer, it follows that conditional formatting is a cell property and the gap between a value and desired formatting is bridged with a formula (in the general case) or a value (in the simple self-reference case).

[oweng]

Yeah, my initial answer was basically just a routine pointing out of the options. I will edit it to include related bugs / enhancements and a possibility.