Ask Your Question

Complex ConditionalFormatting ignored by calc? [closed]

asked 2012-10-25 16:11:29 +0200

Gaffer gravatar image

updated 2012-10-25 16:20:00 +0200

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", ),_
        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", ),_
        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
   Dim oPropertyValue As New
   If Not IsMissing( cName ) Then
      oPropertyValue.Name = cName
   If Not IsMissing( uValue ) Then
      oPropertyValue.Value = uValue
   MakePropertyValue() = oPropertyValue
End Function
edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-24 03:00:58.974059


  1. If Recalculate (F9) not worked, try Ctrl+Shift+F9 (Recalculate Hard)... What the result?
  2. Try change comma to semicolon: mod(row() ; 2)
JohnSUN gravatar imageJohnSUN ( 2012-10-26 09:51:28 +0200 )edit

Bingo! Changing mod(row() , 2) to mod(row() ; 2) makes all the difference. Would you agree that this is a bug? if so, I'll put together a more specific test case and submit it.

Gaffer gravatar imageGaffer ( 2012-10-26 11:39:35 +0200 )edit

Bug? I'm not sure... Tools - Options - LibreOffice Calc - Formula - Separators

JohnSUN gravatar imageJohnSUN ( 2012-10-26 11:55:11 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2013-02-26 23:35:15 +0200

qubit gravatar image

Hi @Gaffer,

I agree with @JohnSUN -- this could just be an issue with different separators used in Calc.

If you think that there is, in fact, a problem with Calc, please file a bug and provide a list of steps that can reproduce your problem. The QA team will be happy to help you track down this issue!

Please post a link to any bugs you file in a comment below using the format "fdo#123456".


edit flag offensive delete link more

Question Tools

1 follower


Asked: 2012-10-25 16:11:29 +0200

Seen: 528 times

Last updated: Feb 26 '13