Conditional formatting in OpenOffice.org

What is conditional formatting? It means applying certain attributes to a spreadsheet cell (e.g. background, font size, ...) depending on some conditions being met or not. The conditions are based on the content of the cell or - in more complex cases - on other cells.

In OpenOffice.org there are two steps necessary:

• Create appropriate styles (to be chosen from)
• Set the conditional formatting for selected cells

This is different to Excel where one selects cells, sets the condition and chooses a format. In my view OpenOffice.org is more flexible because of its simplicity of change: if you want to change the format simply edit the style and the change is applied to wherever the style is being used, be it part of the conditional formatting or elsewhere. (I'm not an Excel expert though.)

Create the appropriate styles

• Click F11 or Format->Styles and Formatting
• Choose cell styles
• Move the mouse to the blank middle of the styles window and right click and press 'New...'
• Choose a style name e.g. RedBg, then choose the Background tab and set your colour, press OK or change any other attribute e.g. font type and size in the Font tab.

Do this for as many styles as you need, so that you have e.g. 3 new named styles for various backgrounds likeRedBg, GreenBg, AmberBg

Set the conditional formatting

• Select the cell or cell range
• Choose Format -> Conditional Formatting...
• Set the conditions
• Example based on numeric values of the cells
    condition 1: 'cell value is' less than 10 and select style RedBg
    condition 2: 'cell value is' less than 20 and select style AmberBg
    condition 3: 'cell value is' greater than or equal to 20 and select style GreenBg
  This also shows how to use conditions in ascending order: if the first condition is met then its style is applied. If not then the second condition will be checked aso.
• Example based on textual values of the cells
    condition 1 'formula is' exact(lower(c5);"no") and select style RedBg
    condition 2 'formula is' exact(lower(c5);"yes") and select style GreenBg
  The background will be set to either red or green depending on no/yes entries in whichever case.
  Important to note: the cell reference c5 should be the lower right hand corner of your cell range

More complex cases are to use conditional formatting based on the entries of other cells or multiple cells, you'd need to construct the conditions as formulas using the appropriate functions.

If you need more than 3 conditions I'm afraid only a macro will get you going.