Conditional Formatting within a Calc Macro

[Gaffer]

This is a short BASIC Calc macro intended to build a simple spreadsheet with multiple “conditional formatting” cells. It runs cleanly but does not create the conditional formats. Clearly I am misunderstanding something important. A pointer would be appreciated.

Sub Main
    oDoc = ThisComponent
    oDoc.sheets(0).getCellByPosition(0, 0).setFormula("5")
    oDoc.sheets(0).getCellByPosition(1, 0).setFormula("Format me")
    oDoc.sheets(0).getCellByPosition(1, 0).ConditionalFormat.addNew( Array( _
    	MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_
    	MakePropertyValue( "Formula1", "A1=5" ), _
    	MakePropertyValue( "StyleName", "Heading" ) ) 
    oDoc.sheets(0).getCellByPosition(0, 1).setFormula("5")
    oDoc.sheets(0).getCellByPosition(1, 1).setFormula("Format me")
    oDoc.sheets(0).getCellByPosition(1, 1).ConditionalFormat.addNew( Array( _
    	MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ), _
    	MakePropertyValue( "Formula1", "A2=5" ), _
    	MakePropertyValue( "StyleName", "Result" ) ) 
End Sub
Function MakePropertyValue( Optional cName As String, Optional uValue ) As com.sun.star.beans.PropertyValue
   Dim oPropertyValue As New com.sun.star.beans.PropertyValue
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   EndIf
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   EndIf
   MakePropertyValue() = oPropertyValue
End Function

[JohnSUN]

Try to save the object ConditionalFormat for this cell in a separate variable. Change its content using Clear and AddNew (or getByIndex and setters). And give back to the property ConditionalFormat for this cell with setPropertyValue(“ConditionalFormat”,…)

...
    oldCondFormat = oDoc.sheets(0).ConditionalFormat
    oldCondFormat.addNew( Array( _
        MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_
        MakePropertyValue( "Formula1", "A1=5" ), _
        MakePropertyValue( "SourcePosition", oDoc.sheets(0).getCellByPosition(1, 0).getCellAddress() ), _
        MakePropertyValue( "StyleName", "Heading" ))) 
    oDoc.sheets(0).getCellByPosition(1, 0).setPropertyValue("ConditionalFormat", oldCondFormat)
...

[Gaffer]

Excellent. Thankyou.

[Gaffer]

This is a great answer but i don;t understand why in the first line of your example code you chose oDoc.sheets(0).ConditionalFormat, which looks to me like a conditional format associated with the whole sheet rather than something like oDoc.sheets(0).getCellByPosition(1,0).ConditionalFormat ?

[JohnSUN]

Oh, sorry! It’s just an unfortunate typo. Of course, you are absolutely right: for the conditional format the entire sheet should be used for reading and appropriation of this sheet, and for individual cells - their own conditional format. Their structure and methods are the same at now…