[Solved] Change background colour of cells based on integer

Discuss the spreadsheet application
Post Reply
Zaphod Beebledoc
Posts: 5
Joined: Sat Dec 07, 2013 12:16 pm

[Solved] Change background colour of cells based on integer

Post 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...
Last edited by Zaphod Beebledoc on Tue Feb 28, 2017 3:27 am, edited 1 time in total.
Apache OpenOffice 4.1.3
AOO413m1(Build:9783) - Rev. 1761381
2016-09-29 02:39:19

LibreOffice 5.2.5.1
Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22

Windows 10 Home 64-bit (1607, Build 14393.576)
User avatar
RusselB
Moderator
Posts: 6561
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Change background colour of cells based on integer

Post 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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Zaphod Beebledoc
Posts: 5
Joined: Sat Dec 07, 2013 12:16 pm

Re: Change background colour of cells based on integer

Post by Zaphod Beebledoc »

That's where I looked first. :)
Apache OpenOffice 4.1.3
AOO413m1(Build:9783) - Rev. 1761381
2016-09-29 02:39:19

LibreOffice 5.2.5.1
Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22

Windows 10 Home 64-bit (1607, Build 14393.576)
User avatar
Lupp
Volunteer
Posts: 3202
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Change background colour of cells based on integer

Post by Lupp »

Suppose we are talking of the value of cell A1. The standard way to test it for being integer is

Code: Select all

=(A1=INT(A1))
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.
On Windows 10: LibreOffice 7.2 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Zaphod Beebledoc
Posts: 5
Joined: Sat Dec 07, 2013 12:16 pm

Re: Change background colour of cells based on integer

Post 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.
Apache OpenOffice 4.1.3
AOO413m1(Build:9783) - Rev. 1761381
2016-09-29 02:39:19

LibreOffice 5.2.5.1
Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22

Windows 10 Home 64-bit (1607, Build 14393.576)
User avatar
Lupp
Volunteer
Posts: 3202
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Change background colour of cells based on integer

Post 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!)
On Windows 10: LibreOffice 7.2 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
RusselB
Moderator
Posts: 6561
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Change background colour of cells based on integer

Post 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.
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Zaphod Beebledoc
Posts: 5
Joined: Sat Dec 07, 2013 12:16 pm

Re: Change background colour of cells based on integer

Post 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)
Apache OpenOffice 4.1.3
AOO413m1(Build:9783) - Rev. 1761381
2016-09-29 02:39:19

LibreOffice 5.2.5.1
Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22

Windows 10 Home 64-bit (1607, Build 14393.576)
Zaphod Beebledoc
Posts: 5
Joined: Sat Dec 07, 2013 12:16 pm

Re: Change background colour of cells based on integer

Post by Zaphod Beebledoc »

Lupp wrote:(I wrongly assumed you knew the 'Formula is' mode of CF. Sorry!)
A RTFM error on my part...
Apache OpenOffice 4.1.3
AOO413m1(Build:9783) - Rev. 1761381
2016-09-29 02:39:19

LibreOffice 5.2.5.1
Build ID: 0312e1a284a7d50ca85a365c316c7abbf20a4d22

Windows 10 Home 64-bit (1607, Build 14393.576)
Post Reply