Ask Your Question

Macro for 3-color scale condition formatting

asked 2018-06-02 22:47:35 +0100

Orbeman gravatar image


I would like to format a column with a 3-color scale condition. I need to do it programmably, because the range changes when I insert new cell values (in other words, I don't want the conditional formatting to affect the same range, but find a way to manage the "gaps" by updating the range). Could someone help me?

PS I already have a good knowledge in VBA coding with Excel, but not really in any language with LibreOffice (on Linux).

Many thanks in advance!

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2018-06-03 06:50:43 +0100

pierre-yves samyn gravatar image

updated 2018-06-03 06:51:11 +0100


If I understand the question correctly, this is one of the examples I made on the macro section of the wiki (on an idea by Michel Rudelle) (sorry, it's in French, I had considered translating this section but ...).

That said there is a spreadsheet example that you can download, so it should be understandable...

The operation is simple: put in G5 and G16 the min & max colors then click on the button to apply the color scale to the range A6: A15

HTH - Regards

edit flag offensive delete link more

answered 2018-06-03 13:06:16 +0100

kompilainenn gravatar image

may be just make the range for conditional formatting larger than now there are values and for empty cell needs to make additional condition:

Cell value is - equal to - "" with style - Default

and move it to top in list

in this case your condition will work how you wrote

edit flag offensive delete link more


For the time being, I think I'll loop through all the cells I want to add a background colour and compute the corresponding RGB values. It'll also give me the possibility to define non-linear relations (e.g. log or more than 3 colours). But I'm afraid it won't be really fast as I have a few thousands values to loop through, so I'll still try to find a way to update the conditional formatting range. Any help will still be welcome :-)

Orbeman gravatar imageOrbeman ( 2018-06-03 16:27:11 +0100 )edit

may be you will talk us about your first target? What want you do with your data?

kompilainenn gravatar imagekompilainenn ( 2018-06-03 18:02:13 +0100 )edit

Thanks, but I'd rather not say what the data are used for, even if it'd probably make my query much easier to understand.

Now I still need to think about how to extend the 3-colour scale to the full rambow range.. By the way, I'm finally not sure if using the standard formatting options would have been quicker to update, because I need to loop through all rows anyway.

Orbeman gravatar imageOrbeman ( 2018-06-09 08:49:09 +0100 )edit

answered 2018-06-03 10:36:20 +0100

Orbeman gravatar image


Thanks for your reply, but I don't really think it corresponds to what I'm looking for :-(

So far, the only clue I've found comes from following thread, but I'd like to use a 3-color scale condition, instead of a formula.

PS No worry for translation as I'm French by the way ^^ I also upgraded my LibreOffice version from 5.3 something to, but the record doesn't really help (it just records the display of the formatting condition frame, but not the different settings..)

Thanks again!

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-02 22:47:35 +0100

Seen: 498 times

Last updated: Jun 03 '18