It seems that a macro that creates a simple ConditionalFormat with a formula that can be immediately calculated, such as “TRUE”, works as expected. However, something more complex such as a formula containing “row()” fails. Oddly, when I open the ConditionalFormatting dialog, the formula is there and if I click OK, the formatting is applied(!)
The macro below creates a simple spreadsheet with 2 columns. The cells in Column A are individually conditionally formatted to “Heading” if the simple formula “TRUE” is true (which is always is…)
The cells in Column B are the problem. The ConditionalFormat is “mod(row(),2)” which is true only on odd rows, so every odd row should be in “Result” format. When the macro populates the cells, the conditional formatting instructions appear to be applied but the formatting is not updated. Look at conditional formatting in cell B1 and you can see the formula there. Press “OK” and the formatting is applied (the cell is formatted as “Result”). To see how it should look, copy B1 into the column below.
I have tried Recalculate (F9) but that doesn’t help.
What am I doing wrong?
Sub Main oDoc = ThisComponent For iRow = 0 To 20 oDoc.sheets(0).getCellByPosition(0, iRow).setFormula("Simple") oldCondFormat = oDoc.sheets(0).getCellByPosition(0, iRow).ConditionalFormat oldCondFormat.addNew( Array( _ MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_ MakePropertyValue( "Formula1", "TRUE"), _ MakePropertyValue( "SourcePosition", oDoc.sheets(0).getCellByPosition(0, iRow).getCellAddress() ), _ MakePropertyValue( "StyleName", "Heading" ) ) oDoc.sheets(0).getCellByPosition(0, iRow).setPropertyValue("ConditionalFormat", oldCondFormat) oDoc.sheets(0).getCellByPosition(1, iRow).setFormula("Complex") oldCondFormat = oDoc.sheets(0).getCellByPosition(1, iRow).ConditionalFormat oldCondFormat.addNew( Array( _ MakePropertyValue( "Operator", com.sun.star.sheet.ConditionOperator.FORMULA ),_ MakePropertyValue( "Formula1", "mod(row(),2)"), _ MakePropertyValue( "SourcePosition", oDoc.sheets(0).getCellByPosition(1, iRow).getCellAddress() ), _ MakePropertyValue( "StyleName", "Result" ) ) oDoc.sheets(0).getCellByPosition(1, iRow).setPropertyValue("ConditionalFormat", oldCondFormat) next iRow 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