[CALC] Pulling the background color of a conditionally formatted cell in VBA

[jasonman101]

Is there a way to get the background color of a cell that has Conditional Formatting - Color Scale applied to it, using VBA? I’ve tried but it’s not working properly, and it seems that Conditional Formatting does not actually set the background color in the normal way.

I have a range of values, and I set up a very basic stoplight conditional formatting rule: Min is Red, 0 is Yellow, and Max is Green. That works properly. I then created a function in VBA that should pull the background color from a cell and return the decimal value. Instead, it only returns -1, which is the result for No Fill cells. I also checked the background transparency, and it returns 1, meaning the cell is transparent. My VBA code is based on Cell.CellBackColor but maybe there’s another option I’m missing.

My ultimate goal was to use the output of my custom function for a color range in a Chart.

Attaching a simple example
conditional.ods (33.5 KB)

System: Win11
LibreOffice Calc 7.4.2.3 (x64)

[Zizi64]

A short answer: there is not.

Longer answer (and questions):
Do you really want to use the MS VBA with the LibreOffice, or you meant the API of the LO and the StarBasic?
My opinion: it is a wrong idea to use the formatting properties as some data.
You must repeat the process related to the conditions, if you want determine the result (the applied cell style) of the Conditional format feature. You can not get the result directly by a macro. Therefoe it is simplier to use helper cells what contains flags (strings or numbers) what will control the CF feature, and gives you informations (directly) about the result of the conditions.

You can get by macro:

• the manual formatting properties (for example the background color of the cell)
• the name of the primary applied Cell Style
  only.

But the result of the CF is not available by macros.

[Villeroy]

t85852.ods (42.3 KB)

[Lupp]

@jasonman101

… a cell that has Conditional Formatting - Color Scale applied to it, …

The concept of ConditionalFormatting in Calc is to conditionally overlay attributes defined via a named cell style to the respective cells inside the current view without changig their actually assigned attributes. The conditions are not generally evaluated for the complete CF range, Evaluation takes place for cells going to enter that view. The result is not accessible via the API.

Toys assembled under the variant All Cells of CF don’t fit into the mentioned concept anyway. As any kind of CF: that’s provided as a means to concisely give the user a visualizaion of some information. It’s definitely not made for automated secondary evaluation.

As @villeroy exemplified: Calculate actual results representing the information (level of alert e.g.) you want to get. You can easily apply “classic” CF then in addition.