How to Change Cell Background Color by Cell Value in Open Office

First, the Reasonable Question.

Why would anyone want the background color of the cells in their spreadsheet to change with different values of data?

Sometimes it is convenient.  Suppose you are tracking multiple lab values across time.  Wouldn’t it be great to have a low value be blue, WNL green, and high value pastel red?

With changing colors, you can tell at a glance what the data trends are for multiple measurements. 

If your patient's Potassium starts low and keeps climbing, you could, for example, see a color change from Low Blue to Green to Red.  Just a glance tells you to pay attention, where bare black numbers on a white background might not catch your attention.

 And if you want to pay attention to particular numbers, the data number is still going to be in the cell for review.

This feature can be applied to other types of data as well.  I use it in my CE-tracking spreadsheet to keep track of multiple finicky sub-requirements in the three regions for which I am licensed.

Here's how you do it:

1)      Define Styles to match your lab value ranges.

I defined them as:  CritLow = Aquamarine, Low= Light Grey, WNL=Green  Hi=pink Crit Hi = Red

Create a Style:

Go to Format à Styles and Formatting [F11].

Click ‘Cell Styles’ icon in upper left of dialog box.

Use DownArrow to select ‘Custom Styles’ at bottom of dialog box.

Right-Click in Dialog Box, then click on [New] Popup.

Name:  LowValue

Linked With:  Default

Category: Custom Styles

Select ‘Background’ from tabs at top of dialog box.  A color palette appears.  I selected ‘Gray1’.  The color appears in the sample box at right.

Click [OK] at bottom of Cell Style Box to save.

You can also specify italics, bold text, etc with other tabs at the top of the Cell Style Box.

2)      Enter high and low values for your labs in date range columns. 

For example, ANC might have a normal range of 2 to 10, so the Low Value column would be “2” and the High Value Column would be “10.”  For this example, suppose that the Low Value cell address is B6 and the High Value cell address is C6.

Enter “1” into A6 for Critically Low Value definition.

3)      Click or highlight the cell(s) to apply formatting.

If the lab result dates are in a row from left to right, you would highlight, in this case, Row 6 from D6 onward to the right, since it will contain your lab data.

Click Format/Conditional Formatting

      You can have up to 3 conditions.  For this example, we will use LowValue, CritLow, and HiValue.

            Condition 1

            Be sure box is checked.

            Cell value is:    less than          $B6  -- Because we want to refer to the low range value as a fixed address in this column.  This will allow us to copy the settings horizontally if needed.  $B$6 would also work, but does not allow us to copy settings downward for other rows of lab values without manually entering the formulas for each row.

Cell Style:  LowValue.

Check the ‘Condition 2’ box.

Cell Value is :  Less than   $A6

Cell Style: CritLow

Check the ‘Condition 3’ box.

Cell Value is;  Greater than  $C6

Cell Style   HiValue

Click OK to save settings.

4)      Test the settings.

Enter values into the boxes to make sure the ranges are set up correctly.

If you have another row of lab values with different Crit Low, Low and Hi values, you can check the copyability of the settings by copying the data line downward and seeing if the new line obeys the limits defined by hi and low values.