Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

Hi

Ok, you can not do that (expected: "com.sun.star.sheet.ConditionOperator") but it seems I found a workaround:

  • create the conditional formating with an accepted operator (e.g. EQUAL), then...
  • "go up" to the sheet level to browse the defined formats and...
  • modify the one for the range we are interested in.

In my example the TeamTable name refers to this cell range: $Tiebreakers.$A$1:$A$11 (adapt to your case). I do not have much time so this name written "hard". One could of course improve e.g. by retrieving these references from the name.

sub formatTeamTable2
    breaks = ThisComponent.sheets.getByName("Tiebreakers")
    table = breaks.getCellRangeByName("TeamTable")
    cf = table.getPropertyValue("ConditionalFormat")
    cf.Clear()
    dim condition(3) as new com.sun.star.beans.PropertyValue
    condition(0).Name = "Operator"
    condition(0).Value = com.sun.star.sheet.ConditionOperator.EQUAL
    condition(1).Name = "Formula1"
    condition(1).Value = """Tied"""
    condition(2).Name = "StyleName"
    condition(2).Value = "Tied"
    cf.addNew(condition)
    table.setPropertyValue("ConditionalFormat", cf)
    oCFS = breaks.ConditionalFormats()
    odummy = oCFS.getConditionalFormats()
    for i = 0 to oCFS.length -1
        oneCF = oDummy(i)
        if oneCF.range.absolutename = "$Tiebreakers.$A$1:$A$11" then
            oMyCF = oneCF.getByIndex(0) 'just created so only one condition'
            oMyCF.setPropertyValue("Operator",  com.sun.star.sheet.ConditionFormatOperator.CONTAINS)
            exit for
        end if
    next i

end sub

Regards