[Solved] Cell Color Control based on Value

Discuss the spreadsheet application
Post Reply
Lew_Merrick
Posts: 33
Joined: Tue May 04, 2010 7:57 am

[Solved] Cell Color Control based on Value

Post by Lew_Merrick »

Yet Another Dumb Question:

I am working on a Calc application. The result (numeric) value is either: good (< some value), possibly problematic (> good but < another value), or bad (> possibly problematic). Is there a simple way to change the background color of the cell into which the value is posted (say, green for good, orange for possibly problematic, and pink for bad)? This would make the interpretation of the results clearer.

Irrational minds want to know...
Last edited by Hagar Delest on Mon Jun 11, 2012 9:01 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.2.0 on Windows Vista
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Cell Color Control based on Value

Post by acknak »

Format > Conditional Formatting

You define a style for each condition; each style will have a different background color setting.
AOO4/LO5 • Linux • Fedora 23
Lew_Merrick
Posts: 33
Joined: Tue May 04, 2010 7:57 am

Re: Cell Color Control based on Value

Post by Lew_Merrick »

Thanks -- It will be a day or two before I can try it out (work is the curse of the thinking class).
OpenOffice 3.2.0 on Windows Vista
Lew_Merrick
Posts: 33
Joined: Tue May 04, 2010 7:57 am

Re: Cell Color Control based on Value

Post by Lew_Merrick »

As shown in OO-Calc
As shown in OO-Calc
O-Ring-01.PNG (15.5 KiB) Viewed 35384 times
Acknak -- Conditional formatting works within the spreadsheet. But, when I copy the cell-set of the form to an OO-Writer document (so I can have history for my work), the cell colors always turn into the last condition (which is, in this case, this does not work) when pasted into my document. Is there a technique or work-around that gets me the correct cell color in a pasted image file?

Irrational minds want to know...
Attachments
As shown in OO-Writer
As shown in OO-Writer
OpenOffice 3.2.0 on Windows Vista
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Cell Color Control based on Value

Post by acknak »

Are you pasting with a simple paste, or are you using Paste Special and choosing some specific data format?

If you're using the plain paste, you should get an embedded spreadsheet, which should support conditional formatting. Maybe a bug?

If you really want a record of the data/results, I think I would paste the cells as an image (Edit > Paste Special > As: bitmap. That should give you a literal snapshot of the exact appearance of the results. Np chance for them to change.

The only down side would be that OOo Writer does sometimes "lose" embedded images, so make sure you create regular backup copies of the document.
AOO4/LO5 • Linux • Fedora 23
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Cell Color Control based on Value

Post by Charlie Young »

Lew_Merrick wrote:Acknak -- Conditional formatting works within the spreadsheet. But, when I copy the cell-set of the form to an OO-Writer document (so I can have history for my work), the cell colors al=ways turn into the last condition (which is, in this case, this does not work) when pasted into my document. Is there a technique or work-around that gets me the correct cell color in a pasted image file?

Irrational minds want to know...
Similar problems have come up before: viewtopic.php?f=9&t=43083&p=198721

I had some PM exchanges with dummydecoy as a result of that. I wound up writing some rather tricky macros to do the copying. There were also some strange problems about the formats only copying correctly if the cells were visible on the screen during the copy.

If necessary, I can try to dust off some of that stuff, I think I still have all the pieces. But before we get into that, how are you doing the copying/pasting? Is it paste special of the graphics, or a straight paste of the Calc cells?
Apache OpenOffice 4.1.1
Windows XP
User avatar
Charlie Young
Volunteer
Posts: 1559
Joined: Fri May 14, 2010 1:07 am

Re: Cell Color Control based on Value

Post by Charlie Young »

acknak wrote:Are you pasting with a simple paste, or are you using Paste Special and choosing some specific data format?

If you're using the plain paste, you should get an embedded spreadsheet, which should support conditional formatting. Maybe a bug?

If you really want a record of the data/results, I think I would paste the cells as an image (Edit > Paste Special > As: bitmap. That should give you a literal snapshot of the exact appearance of the results. Np chance for them to change.

The only down side would be that OOo Writer does sometimes "lose" embedded images, so make sure you create regular backup copies of the document.
As I recall, the pasted image had problems, which is what I wrote the macros to deal with, and a problem with the embedded spreadsheet is it can lose references on which the conditional formatting is based.
Apache OpenOffice 4.1.1
Windows XP
Lew_Merrick
Posts: 33
Joined: Tue May 04, 2010 7:57 am

Re: Cell Color Control based on Value

Post by Lew_Merrick »

acknak wrote:Are you pasting with a simple paste, or are you using Paste Special and choosing some specific data format?

If you're using the plain paste, you should get an embedded spreadsheet, which should support conditional formatting. Maybe a bug?
I am doing a highlight the form, <Ctrl>C, alter focus, and <Ctrl>V cut and paste. As the images should tell you, this is an o-ring gland (groove) calculating system. The form tells you: the design bore (shaft, in a different form) size, application & design squeeze factor, standard o-ring identity & dimensional size, data for constructing a CAD model of the installed o-ring, gland dimensions, and installation & operational stretch values. If the stretch values are bad, the background is (brick) red (I am dead colorblind to real red, so I avoid it). If the stretch values are borderline (maybe good, maybe not), the background is orange. If the stretch values are good, then the background is green.

Now, interestingly enough, it copies properly into PAINT. ???
OpenOffice 3.2.0 on Windows Vista
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Cell Color Control based on Value

Post by acknak »

Lew_Merrick wrote:...Now, interestingly enough, it copies properly into PAINT. ???
I'm guessing that's because Paint is negotiating a bitmap copy, not the embedded object you get (by default) within OOo.

If you copy/paste the cells into OOo Draw/Impress, you get the data as a table of text values--just to say that different applications can give different behavior on copy/paste.

Try copying the bitmap into Writer, as I mentioned above, and see if that looks correct. Maybe the problems Charlie mentioned have been fixed, or don't cause a problem in your sheet.
AOO4/LO5 • Linux • Fedora 23
Lew_Merrick
Posts: 33
Joined: Tue May 04, 2010 7:57 am

[Solved] Re: Cell Color Control based on Value

Post by Lew_Merrick »

acknak wrote:Try copying the bitmap into Writer, as I mentioned above, and see if that looks correct. Maybe the problems Charlie mentioned have been fixed, or don't cause a problem in your sheet.
Yup, that works. Problem solved!
OpenOffice 3.2.0 on Windows Vista
Post Reply