[Solved] Cell Color Control based on Value

[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...

[acknak]

Format > Conditional Formatting

You define a style for each condition; each style will have a different background color setting.

[Lew_Merrick]

Thanks -- It will be a day or two before I can try it out (work is the curse of the thinking class).

[Lew_Merrick]

As shown in OO-Calc

O-Ring-01.PNG (15.5 KiB) Viewed 35390 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...

[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.

[Charlie Young]

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?

[Charlie Young]

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.

[Lew_Merrick]

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. ???

[acknak]

...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.

[Lew_Merrick]

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!