Macro Conditional Formatting always results in = condition

Hedaja1 · May 26, 2025, 5:17pm

Hi everyone,
I’m trying to do some conditional formatting using a macro.
It works fine for the first condition (>=).
But whatever I change the second operator to, it always falls back to EQUAL = (even though I can see the number change in my oCondition)

Here is the part of my script that handles the formatting:

' Farben für Conditional format
Dim clrRed As Long
Dim clrYellow As Long
Dim clrGreen As Long
Dim clrBlue As Long

clrRed = RGB(255, 185, 185)
clrYellow = RGB(255, 254, 169)
clrGreen = RGB(225, 255, 190)
clrGreen = RGB(225, 255, 190)
clrBlue = RGB(211, 223, 252)

'
  Dim oConFormat
  Dim oCondition(3) As New com.sun.star.beans.PropertyValue

    oCellRange.ConditionalFormat = Array()

    ' Rule 1: Yellow if value >= 0.5
     oConFormat = oCellRange.ConditionalFormat
  oCondition(0).Name = "Operator"
  oCondition(0).Value = com.sun.star.sheet.ConditionFormatOperator.GREATER_EQUAL
  oCondition(1).Name = "Formula1"
  oCondition(1).Value = "0.5"
  oCondition(2).Name = "StyleName"
  oCondition(2).Value = "check"
  oCondition(3).Name = "SourcePosition"
  oCondition(3).Value = oCellRange.getCellByPosition(0,0).getCellAddress
  oConFormat.addNew(oCondition())
  oCellRange.ConditionalFormat = oConFormat
  
   ' Rule 1: Yellow if value >= 0.5
     oConFormat = oCellRange.ConditionalFormat
  oCondition(0).Name = "Operator"
  oCondition(0).Value = com.sun.star.sheet.ConditionFormatOperator.LESS
  oCondition(1).Name = "Formula1"
  oCondition(1).Value = "0.5"
  oCondition(2).Name = "StyleName"
  oCondition(2).Value = "Bad"
  oCondition(3).Name = "SourcePosition"
  oCondition(3).Value = oCellRange.getCellByPosition(0,0).getCellAddress
  oConFormat.addNew(oCondition())
  oCellRange.ConditionalFormat = oConFormat

I’ll attach the full file and the Test file in case it’s needed
Fledermaus-Makro v2.2_fuer_pippyg_LibreOffice.ots (162.4 KB)
Can’t upload csv so here’s the raw data:

FilePath,FileName,Date,Time,Bbar,Malc,Mbec,MbraMmys,Mdau,Mnat,NSL,Paur,Ppip,Ppyg,Rfer,Rhip
D:/Test/gefiltert,DEV_056_20250505_204152_000,NA,NA,0.50,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_056_20250505_204544_000,NA,NA,0.00,0.50,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_056_20250505_204748_000,NA,NA,0.00,0.00,0.50,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_056_20250505_204836_000,NA,NA,0.00,0.00,0.00,0.50,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_056_20250505_210121_000,NA,NA,0.00,0.00,0.00,0.00,0.50,0.00,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_056_20250505_213456_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.50,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_204002_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.90,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_204543_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.50,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_205120_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.92,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_205133_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.92,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210301_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.50,0.00
D:/Test/gefiltert,DEV_062_20250506_210314_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.50
D:/Test/gefiltert,DEV_062_20250506_210329_000,NA,NA,0.50,0.50,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210346_000,NA,NA,0.00,0.50,0.50,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210402_000,NA,NA,0.00,0.00,0.50,0.50,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210424_000,NA,NA,0.00,0.00,0.00,0.50,0.50,0.00,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210726_000,NA,NA,0.00,0.00,0.00,0.00,0.50,0.50,0.00,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210742_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.50,0.90,0.00,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210756_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.90,0.50,0.00,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210806_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.50,0.92,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210828_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.92,0.92,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_210843_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.92,0.50,0.00
D:/Test/gefiltert,DEV_062_20250506_210908_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.50,0.50
D:/Test/gefiltert,DEV_062_20250506_210920_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.50
D:/Test/gefiltert,DEV_062_20250506_211634_000,NA,NA,0.00,0.00,0.00,0.00,0.00,0.00,0.60,0.00,0.60,0.00,0.00,0.00
D:/Test/gefiltert,DEV_062_20250506_213754_000,NA,NA,0.40,0.40,0.40,0.40,0.40,0.40,0.40,0.40,0.40,0.40,0.40,0.40
D:/Test/gefiltert,DEV_062_20250506_213754_000,NA,NA,0.50,0.51,0.52,0.53,0.54,0.55,0.56,0.57,0.58,0.59,0.60,0.61

Thank you in advance

JohnSUN · May 26, 2025, 5:29pm

Please try
oCondition(0).Value = com.sun.star.sheet.ConditionOperator2.LESS
in line 246

By the way, remark at line 243 - may be <0.5?

Hedaja1 · May 26, 2025, 5:45pm

That did the trick.Thank you John.
Is this like a new operator? I think I have seen the same with TableBorder2.

Yes. Thanks that was a copy paste error =)

JohnSUN · May 26, 2025, 5:49pm

Well, it’s not really that new…

mikekaganski · May 26, 2025, 5:54pm

Actually, addNew takes com.sun.star.sheet.ConditionOperator (and the ConditionOperator2 was created to expand its values; unfortunately, ConditionOperator was defined as enum, and that type doesn’t allow extending; ConditionOperator2 is a constant group, so has no such limitation).

And that ConditionFormatOperator, of course, creates confusion

Villeroy · May 26, 2025, 8:26pm

With conditional formatting of type “Formula is” you get a lot more flexibility. I never use any of the operators.

Hedaja1 · May 26, 2025, 9:09pm

I used a formula at a later point but mainly because I needed to check two cells but I agree that generally it’s just more flexible.
I struggled a bit with it because of the comma decimal separator but got it working in the end.
The only issue I couldn’t solve that I wanted to apply that conditional formatting to multiple ranges that already had some conditional formatting which resulted in the old format being overwritten.
I decided to just say screw it and applied the formatting to a different column

Thanks everyone for the quick and helpful inputs

Villeroy · May 26, 2025, 9:18pm

The formula expression follows the locale setting. With German locale, regardless of UI language, you’ve got to use comma as decimal separator.
With an operator, the condition is a formula expression too.
For instance, you can define “Cell Value” “greater or equal” with AVERAGE($X1:$99) or with formula [Active Cell]>=AVERAGE($X1:$X99) where [Active Cell] is the address of the anchoring cell (property “SourcePosition”).