Page 1 of 1
[Solved] Change background colour of cells based on integer
Posted: Tue Feb 28, 2017 12:20 am
by Zaphod Beebledoc
I want to change the background colour of cells based on if they contain just integers. Can I do this?
Edit: I'm using Apache OpenOffice v4.1.3 on a Windows 10 Home machine, as there might be confusion between AOO and Libre...
Re: Change background colour of cells based on integer
Posted: Tue Feb 28, 2017 12:30 am
by RusselB
My first thought is that this could be done using Format -> Conditional formatting
However, checking the options, I don't see a way to specify that the number must be an integer.
Re: Change background colour of cells based on integer
Posted: Tue Feb 28, 2017 12:36 am
by Zaphod Beebledoc
That's where I looked first.
Re: Change background colour of cells based on integer
Posted: Tue Feb 28, 2017 1:25 am
by Lupp
Suppose we are talking of the value of cell A1. The standard way to test it for being integer is
You may test with any different variant of rounding to an integer as there are (available as standard functions in Calc) ROUND, ROUNDDOWN, ROUNDUP, FLOOR, CEILING (Forgot one?). The "UP" and "DOWN" here are concerning the absolute amount. (Bad idea imo.)
There are many issues, however. Technical, mathematical, behavourial ...
You should ask simpler questions.
As long as you are working with tiny numbers up to an amount of about 2^49 (562949953421312), the conversion issue should not be significant. But there may occur very serious errors due to different issues, if the distinction between integer and not integer is critical. Calc cannot find mathematical facts about the property.
Re: Change background colour of cells based on integer
Posted: Tue Feb 28, 2017 1:53 am
by Zaphod Beebledoc
Found it! In the Conditional Formatting dialogue, you can select between All Cells, Cell Value is, Formula is, and Date is.
If you select Formula is and =Int(A1)=A1, then apply the formatting you want, in my case a green background, that will do the trick.
Re: Change background colour of cells based on integer
Posted: Tue Feb 28, 2017 2:03 am
by Lupp
Yes, of course. This will not make vanish the issues I talked of.
(I wrongly assumed you knew the 'Formula is' mode of CF. Sorry!)
Re: Change background colour of cells based on integer
Posted: Tue Feb 28, 2017 2:36 am
by RusselB
Zaphod Beebledoc wrote:Found it! In the Conditional Formatting dialogue, you can select between All Cells, Cell Value is, Formula is, and Date is.
If you select Formula is and =Int(A1)=A1, then apply the formatting you want, in my case a green background, that will do the trick.
Interesting... my version of Apache OpenOffice 4.1.3 only has the Cell Value is and Formula is options.
Re: Change background colour of cells based on integer
Posted: Tue Feb 28, 2017 2:47 am
by Zaphod Beebledoc
RusselB wrote:Zaphod Beebledoc wrote:Found it! In the Conditional Formatting dialogue, you can select between All Cells, Cell Value is, Formula is, and Date is.
If you select Formula is and =Int(A1)=A1, then apply the formatting you want, in my case a green background, that will do the trick.
Interesting... my version of Apache OpenOffice 4.1.3 only has the Cell Value is and Formula is options.
Ah yes, just realised I was using LibreOffice for this spreadsheet. I use both software suites. I need to update my signature as well... (updated)
Re: Change background colour of cells based on integer
Posted: Tue Feb 28, 2017 3:30 am
by Zaphod Beebledoc
Lupp wrote:(I wrongly assumed you knew the 'Formula is' mode of CF. Sorry!)
A RTFM error on my part...