Search code examples
excelconditional-formattinglibreoffice-calc

Highlight a cell if the value is greater than the value of the cell from the preceding column

Here is the dataset I am working on.

enter image description here

I want to highlight the transition from 0 to 1 and from 1 to 0.

So a cell should be red if the value of the cell is 1 and the preceding column cell has the value 0. And green when the cell is 0 and the preceding column cell has the value 1

Basically

Transiiton => Color   
0 -> 1 => 1 should be Green  
1 -> 0 => 0 should be Red
Solution
    1. Select A2:J8 (since i assume that the cells in row 1 shouldn't get formatted);
    2. Menu Format -> Conditional -> Condition...
    3. Define two conditions (add the second using Add button after defining the first one):

      1. Cell value -> Is greater than -> A1

        Apply Style: Bad (gives dark red font color on light red background) or your own cell style

      2. Cell value -> Is lesser than -> A1

        Apply Style: Good (gives dark green font color on light green background) or your own cell style.

    Notice that Calc will automatically adapt the cell reference "A1" for all cells in range accordingly. Thus, LO Calc will check the condition for cell B3 against B2 instead of A1.

    Result:

    enter image description here