Atp's external memory

LibreOffice, OpenOffice conditional formatting

note to self for next time.

I needed to have a cell have a yellow or red background colour based on its contents. I also needed a second cell to go yellow or red, depending on whether the first was yellow or red.

The secret is conditional formatting and a new style.

First make a new style.

  • Format -> Styles and formatting
  • right click and select new style
  • set name to "redbackground", set the background to red, and set the right number formatting, press ok
  • Repeat for yellow.

Now for the straight forward case of making the cell background have the correct colour, select the range of cells that are important, and;

  • format->Conditional Formatting
  • Under Condition 1, set "cell value is" "greater than" 700
  • Set Cell Style to "redbackground"
  • Tick Condition 2
  • For condition 2, Set "Cell Value is"  "greater than" 500
  • Set Cell Style to "yellow background"

It seems that the logic stops as soon as it has a match, so you need to put the most restrictive first.



So for the next bit, we need to set the background colour of the cell depending on the value in another cell.

  • Select the new data range
  • Format -> Conditional Formatting
  • Under Condition 1, Change "Cell Value is" to "Formula is" "B2 > 700"
  • Select the Cell Style to be "redbackground"
  • Under Condition 2, Change "Cell Value is" to "Formula is" "B2 > 500"
  • Select the Cell Style to be "yellowbackground"

And here you are;

conditional formatting

Written by atp

Sunday 03 July 2011 at 6:05 pm

Posted in Linux

Leave a Reply