Description: I want to format cell values in green (lower) or red (higher) compared to cells containing values for comparison. Attached is a calc document containing two rows of values where the first row has a conditional formatting rule applied. It is expected the cells in the first row are colored red and green based on the result of the formatting rule - the rule covers the whole cell range with values in this row: On a lower value compared to the cell from the second row the cell background is set to green and on a higher value set to red. The coloring ends after the first cell. After reading the documentation page to this funtionality I'm not sure if the created rule is the correct way to do this, but in my understanding of the documentation it should be. Best Alex Steps to Reproduce: 1. Create the formatting templates from two cells where the first cells background is colored green and the second cell background for the second formatting rule is colored red. Name each formatting template depending on the color: for red give the name "Higher" and for green the name "Lower". 2. Create two rows with about 5 columns of data. 3. Create a conditional formatting rule of the range of cells containing values in the first row. 4. Select Cell value and for the comparison rule lower than and select the cell range for comparison in the range of cell in the second row containing values. 5. Add another subrule set and select as comparison rule higher than and select the same range in the second row. Actual Results: First cell in the first row will be formatted. Expected Results: All cells in the first row will formatted. Reproducible: Always User Profile Reset: No Additional Info: [Information automatically included from LibreOffice] Locale: de Module: SpreadsheetDocument [Information guessed from browser] OS: Windows (All) OS is 64bit: no
Created attachment 191913 [details] Conditional fornatting of cells based on rules for checking values greater than or less than or equal to
I guess your after: CF for A1:E1: 1. Cellvalue »lower|equal« A$2 ⇒ Lower 2. Cellvalue »greater« A$2 ⇒ Higher CF for A2:E2: 1. Cellvalue »lower|equal« A$1 ⇒ Lower 2. Cellvalue »greater« A$1 ⇒ Higher Solved?
More compact: CF for A1:E2: 1. Cellvalue »equal_to« MIN(A$1:A$2) ⇒ Lower 2. Cellvalue »equal_to« MAX(A$1:A$2) ⇒ Higher
Hi Werner as you can see in the attached document, this is what I've configured. Currently only for the range A1:E1 compared with A2:E2. And it is not working. Sorry. Best Alex
AFAIK it is not possible to define a conditional formatting rule where the condition is a range. It has to be either a single cell or the result of a formula. In Excel it is not possible as well. The difference is that Excel throws an error, making it clearer that this is not possible.
Actually Excel offers a way to achieve this: https://techcommunity.microsoft.com/t5/excel/conditional-formatting-row-by-row/m-p/3966290 In Calc the same can be achieved by setting up a Color Scale (Format - Conditional - Color Scale). Then in "Condition" you must choose "Formula is". Then you write "A1<=A2" in the formula and choose the style. Then do the same for the ">" rule. Let me know if this works for you.
Here are 2 good sources for my answer. I'll leave them here for future reference. https://superuser.com/questions/1562267/conditional-formatting-with-libreoffice-calc-activated-with-a-letter-and-the-con https://superuser.com/questions/900618/how-to-color-rows-based-on-two-cell-values-in-open-libre-office
Created attachment 191920 [details] Modified file If I have interpreted fine, what is required. Instead of the second row range in the condition, put $Tabelle1.A$2 Having a relative column, it is adapted by CFF as it evolves on the range with the condition.
(In reply to Alexander Nolting from comment #4) > Hi Werner > > as you can see in the attached document, this is what I've configured. > Currently only for the range A1:E1 compared with A2:E2. > And it is not working. Sorry. > > Best Alex No your CF is different: Cellvalue »lower|equal A2:E2 my suggestion: Cellvalue »lower|equal A2 (you need do declare the CF relative to upper|left Cell in the CellRange !! ))