While I've not found a way to do it as a conditional format,
Here is a way to get the same result.
Sub setCellNumRedFmt( aCell as Object, dayOffset as Integer )
' set number format based on cell.value, color based on previous cell.value
' must test in order of size
' only set 0 or 2 decimal places
Dim v ' val() is a function
prevCell = cellRC(aCell.celladdress.row, aCell.celladdress.column - dayOffset)
v = prevCell.value
If ((-1000 < aCell.Value) And (aCell.Value < 1000)) Then
IF aCell.value = 0 Then
aCell.CellStyle = "blk0"
ElseIF aCell.value < v then
'aCell.CellStyle = "red2"
aCell.setPropertyValue("CellStyle", "red2")
Else
aCell.CellStyle = "blk2"
End if
Else
IF aCell.value < v then
'aCell.CellStyle = "red0"
aCell.setPropertyValue("CellStyle", "red0")
Else
aCell.CellStyle = "blk0"
End if
End If
end Sub
Upon consideration this might be preferable to setting a conditional format in every cell using more memory and disk space.
In another situation where I want a group of cells to take their value from different cell and compare it to a third cell this works.
Sub setCellColorBarlCondForms(cbCell, srcOffset as Integer, offset20DayAvg as integer)
' cbCell is the current colorBar cell whose formula is being set
' srcOffset is the offset from the current cell to the cell being compared to the 20Day average
' avg20DayOffset is the offset from the current colorBar cell to the avg20Day cell
Dim srcAddr As String, avg20dayAddr As String
' srcAddr is the address of the cell whose data is being compared to the 20day average
' avg20dayAddr is the address of the 20day average
' from file:///usr/share/libreoffice/help/en-US/text/scalc/01/04060109.html?&DbPAR=CALC&System=UNIX
' under STYLE definition
' acts on active cell, setting formula and conditional reference cell
' formrc is the address of the cell whose data is being compared to the 20day average
' form1 is the address of the 20day average
srcAddr = "RC[-" & srcOffset & "]"
avg20dayAddr = "RC[" & offset20DayAvg & "]"
' https://wiki.openoffice.org/wiki/Documentation/How_Tos/Calc:_STYLE_function
cbCell.FormulaLocal = "=" & srcAddr & "+STYLE(IF(CURRENT()<" & avg20dayAddr & _
"; ""Red""; ""Grn"" ))"
End Sub
In both cases blk0, blk2, red0, red2 and Red, Grn are styles defined via the Styles menu.
I suspect that a style is based on a struct but have been unable to find a definition in the
docs and not being conversant in either C++ or Java have not tried to dig it out of the source
so creating a style programmatically hasn't happened.
As for clearing a cell's conditional format this I think will work but turned out to be unneeded:
cell.clearContents(com.sun.star.sheet.CellFlags.FORMULA)
Hope this helps some one down the line.
Be well,
Mike