We use Linux at home, and are attempting to put together a generic spread sheet for us to value/research a company's worth.
On my PC I use LibreOffice Calc (version 4.2) as the spreadsheet program. Despite being a computer programmer by profession, I'm very green when it comes to spreadsheet manipulation.
After getting the sums to work on the sheet, what I now want to do, is colour particular cells as a part of a "traffic light" system, i.e. green for a "go for it" value, and red for "don't touch with a barge pole" warning. I found this quite easy to do for distinct cells, i.e. by using creating new "styles" for the background colours (via Format -> Style and Formatting -> New) and then to associate a colour (i.e. style) and a satisfied condition (Format -> Conditional Formatting -> Condition).
But it would seem to me, that in order to arrange the colour of one cell is condition on a relationship of it's value compared to the value of another seems to require a less trivial piece of functionality (VBA macro code, perhaps?).
As an example how would I colour cell C5 green if it's value is at least twice that of the value in cell C3?
Any guidance to whether a VBA macro is the way forward here, and if so how to code it, and then associate that code with the cell (to be coloured) would be a great help.
(LibreOffice Calc is fairly similar to Microsoft Excel, to the best of my knowledge, though after quickly trying to research on youtube, I have a feeling that some areas of the UI may differ).
many thanks
Matt
Got a credit card? use our Credit Card & Finance Calculators
Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site
Conditional cell background colour on LibreOffice calc spreadsheet
-
- Lemon Quarter
- Posts: 3245
- Joined: March 7th, 2018, 8:14 pm
- Has thanked: 2222 times
- Been thanked: 587 times
-
- Lemon Quarter
- Posts: 3245
- Joined: March 7th, 2018, 8:14 pm
- Has thanked: 2222 times
- Been thanked: 587 times
Re: Conditional cell background colour on LibreOffice calc spreadsheet
Sorry I've almost figured this out myself already. In the regular formatting dialog, on a whim, I tried, instead saying:
I wrote
for example. And this worked fine. If there was an easy way to embed screen shots on LF posts, I'd be able to show you what I've done, right now....
"greater than" 10
I wrote
"greater than" 2 * C18
for example. And this worked fine. If there was an easy way to embed screen shots on LF posts, I'd be able to show you what I've done, right now....
-
- Lemon Half
- Posts: 9129
- Joined: November 4th, 2016, 1:16 pm
- Has thanked: 4140 times
- Been thanked: 10023 times
Re: Conditional cell background colour on LibreOffice calc spreadsheet
Melanie wrote:
If there was an easy way to embed screen shots on LF posts, I'd be able to show you what I've done, right now....
Have a read of this thread, where I wrote some step-by-step instructions, and some other really helpful comments were also included by others later on in the thread -
https://www.lemonfool.co.uk/viewtopic.php?f=21&t=11255
When you want to give it a go, it's probably going to be worth having a play on the 'Testing' board first, where you can make sure you've got a working process before committing anything to the main boards -
https://www.lemonfool.co.uk/viewforum.php?f=28
Hope this helps.
Cheers,
Itsallaguess
-
- Lemon Slice
- Posts: 333
- Joined: November 12th, 2016, 9:25 pm
- Has thanked: 566 times
- Been thanked: 125 times
Re: Conditional cell background colour on LibreOffice calc spreadsheet
(VBA macro code, perhaps?)
Macros can be written for LibreOffice, and in various languages. Sadly (?) VBA is not one of them.
Star Basic (LibreOffice basic) works, and it may be easier to rewrite a VBA macro into that form, but Beanshell also an option, as is Python 3.
They have also added JavaScript to the list since I was playing about with that sort of thing.
-
- Lemon Quarter
- Posts: 3245
- Joined: March 7th, 2018, 8:14 pm
- Has thanked: 2222 times
- Been thanked: 587 times
Re: Conditional cell background colour on LibreOffice calc spreadsheet
Itsallaguess wrote:Melanie wrote:
If there was an easy way to embed screen shots on LF posts, I'd be able to show you what I've done, right now....
Have a read of this thread, where I wrote some step-by-step instructions, and some other really helpful comments were also included by others later on in the thread -
https://www.lemonfool.co.uk/viewtopic.php?f=21&t=11255
When you want to give it a go, it's probably going to be worth having a play on the 'Testing' board first, where you can make sure you've got a working process before committing anything to the main boards -
https://www.lemonfool.co.uk/viewforum.php?f=28
Hope this helps.
Cheers,
Itsallaguess
Yes, I guessed that a hosting site might be required somewhere along the way.
I may try a URL from my basic dropbox account.....though seeing as I'm using a basic (free) account, I'm not sure whether the link will be either persistent or public.
Matt
-
- Lemon Quarter
- Posts: 3245
- Joined: March 7th, 2018, 8:14 pm
- Has thanked: 2222 times
- Been thanked: 587 times
Re: Conditional cell background colour on LibreOffice calc spreadsheet
madhatter wrote:(VBA macro code, perhaps?)
Macros can be written for LibreOffice, and in various languages. Sadly (?) VBA is not one of them.
Star Basic (LibreOffice basic) works, and it may be easier to rewrite a VBA macro into that form, but Beanshell also an option, as is Python 3.
They have also added JavaScript to the list since I was playing about with that sort of thing.
TBH, they probably should have stuck with just a complete emulation of VBA if possible, IMO. At least then the online resources would be consistent with those for excel in this area.
Python eh? I'm a reasonably proficient python programmer, though TBH, I shy away from actual programming at home these days....after 25 years doing it in the old day job.
-
- Lemon Slice
- Posts: 491
- Joined: November 6th, 2016, 11:25 am
- Has thanked: 134 times
- Been thanked: 138 times
Re: Conditional cell background colour on LibreOffice calc spreadsheet
May I recommend that in future you post on the OpenOffice (also LibreOffice) forum? https://forum.openoffice.org/en/forum/index.php
You'll get some really good answers from experts, some of whom were involved in the development of the project. Also a search of the forum threads will often yield an answer. There are also tutorials
You'll get some really good answers from experts, some of whom were involved in the development of the project. Also a search of the forum threads will often yield an answer. There are also tutorials
Return to “Technology - Computers, TV, Phones etc.”
Who is online
Users browsing this forum: No registered users and 9 guests