Macro for 3-color scale condition formatting

Hello,

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!

Hi

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

Hello,

Thanks for your reply, but I don’t really think it corresponds to what I’m looking for :frowning:

So far, the only clue I’ve found comes from following thread Conditional Formatting within a Calc Macro, 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 6.0.4.2, but the record doesn’t really help (it just records the display of the formatting condition frame, but not the different settings…)

Thanks again!

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

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 :slight_smile:

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

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.