GNet Solutions » GNU/Linux Administration

I recently needed to have visual feedback for a pricing spreadsheet where I needed to see if the markup of a given price meets the minimum percentage of its originating price. This visual feedback would change the background of a cell to green if its listed price is greater than or equal to its vendor price, if not it will change to red. This is a guide on how to change the background color of a cell based on a conditional formula in LibreOffice Calc.

Create The Styles¶

• First create the "Red" and "Green" background styles. This can be done by going to the top menu and selecting Format -> Styles and Formatting, the same function can be accomplished by pressing the F11 key. This will bring up the Styles and Formatting section on the right hand side of Calc.

• Next right click in the Styles and Formatting section and click New.
  • Name the style "Green", then click the Background tab and select the green color.
  • Click OK when finished setting up the style.

• Next right click in the Styles and Formatting section and click New.
  • Name the style "Red", then click the Background tab and select the red color.
  • Click OK when finished setting up the style.

Apply the Formula¶

• Now that the styles have been setup, select the cell which the formula will be applied to and add the following formula, adjusting to suit your needs:
  

  =A3 + STYLE(IF(D3>=(A3+(A3*0.15)),"Green","Red"))
  

  • NOTE: This will check if cell D3 has a 15% or more markup applied to cell A3 and change the background to green if true. If not, it will set to background color to red.

Resources¶

• http://ask.libreoffice.org/en/question/8041/calc-make-cell-red-if-the-value-is-above-given/
• https://help.libreoffice.org/Calc/Logical_Functions
• https://help.libreoffice.org/Calc/Operators_in_Calc