Skip to main content

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.





Comments

Popular posts from this blog

Installing HR Block Tax Software in Ubuntu Linux using Codeweavers Crossover

Running HR Block 2017 Tax Software in Ubuntu Linux  Using CodeWeavers Crossover 2017 US taxes are due to be filed by midnight on April 18, 2018. It will surprise no one that a lot of people have waited to the last minute to start filing. At 9:30 PM, I got a call from a young relative who needed to do his taxes for the first time ever. He is a Linux enthusiast, and does not have a Windows or Apple/Mac computer. I thought I would try installing HR Block 2017 Tax Software on the Linux side of my laptop. 1) Obtain and install Codeweavers Crossover for Linux This software allows you to run many Windows apps in Linux-- https://www.codeweavers.com/ 2) Download the HR Block Windows tax software package. This year, its available from www.hrblock.com/2017/DELUXEWIN You will need an activation code, which should be provided when you buy the package. The default download location is /home/yourname/Downloads/ The app’s ...

The Big Reset

A lot of people are very upset about global financial conditions these days.   Almost everyone I know is struggling to make ends meet, and failing at it.  Debt levels are high, wages are not keeping up with inflation, our standards of living are going down year by year.  The most likely endpoint of this process, for most of us, seems to be bankruptcy--But bankruptcy is increasingly being prohibited by law.  The only people who seem to be prospering in the current environment are the Very Rich, Government Employees, and Organized Criminals. In the banking crisis in 2008, we bailed out the banks.  It has gotten us nowhere.  The banking industry has continued the same bad practices that got it into trouble in the first place.  We are heading for another crisis of the same kind, but orders of magnitude larger.   The usual endpoint for all this is a giant financial crisis, mass bankruptcy, hyperinflation, chaos, war, bankers and politicians ha...

The dual-boot purple screen - resolved

My laptop has Win 7 Pro on it, and also Ubuntu Linux 16.04.  Windows is a grumpy little OS that feels that it is the only OS in the universe.  Ubuntu Linux realizes that it is not the only OS, just the most trouble-free and flexible-- But Ubuntu does not really care about the issues of other OS's. To get them both on the same laptop, I usually have to install Windows first, then nudge it aside with Ubuntu.  If all goes well, the two systems mostly get along, but it is not elegant. On start up, there is a menu of OS's that allows you to select Ubuntu or Windows.  If I choose Windows, I used to see a purple screen with white dashes for a while, then the Win boot menu would come up and all would be well. After the laptop crashed, I got the purple screen with white dashes, but the Win boot screen did not appear.  I could hear the Win 7 start up tune, but could not see the screen. It turns out that the purple screen with white dashes is an Ubuntu splash scree...