Donate to Remove ads

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

Seek assistance with all types of tech. - computer, phone, TV, heating controls etc.
TheMotorcycleBoy
Lemon Quarter
Posts: 3245
Joined: March 7th, 2018, 8:14 pm
Has thanked: 2222 times
Been thanked: 587 times

Conditional cell background colour on LibreOffice calc spreadsheet

#144590

Postby TheMotorcycleBoy » June 8th, 2018, 7:12 pm

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

TheMotorcycleBoy
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

#144591

Postby TheMotorcycleBoy » June 8th, 2018, 7:26 pm

Sorry I've almost figured this out myself already. In the regular formatting dialog, on a whim, I tried, instead saying:

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

:D

Itsallaguess
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

#144611

Postby Itsallaguess » June 8th, 2018, 10:06 pm

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

madhatter
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

#144630

Postby madhatter » June 9th, 2018, 12:04 am

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

TheMotorcycleBoy
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

#144680

Postby TheMotorcycleBoy » June 9th, 2018, 11:00 am

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

TheMotorcycleBoy
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

#144682

Postby TheMotorcycleBoy » June 9th, 2018, 11:03 am

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

Nocton
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

#144759

Postby Nocton » June 9th, 2018, 7:31 pm

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


Return to “Technology - Computers, TV, Phones etc.”

Who is online

Users browsing this forum: No registered users and 9 guests