In Calc, how do I set conditional format to say if cell value less than RC[-1] then use red text in a macro.

[MikeMcClain46]

I’m converting a stock tracking spreadsheet from Excel to LO Calc and having trouble with coloring cells based on their value based on their relationship to other cells values.
In digging through the docs I see I should define a style but am unclear how to do so programmatically.
Likewise my Excel code dies on ActiveCell.FormatConditions.Delete.
How do you go about clearing the previous conditional format?
Thanks,
Mike

Your site needs work if I have to solve ‘NotARobot’ 5 times just to post a question.

[anon73440385]

Your site needs

It is not “Our”, since we are users of LibreOffice like you are a user of LibreOffice

[schiavinatto]

I did not understand the question well, but for coloring you can use Conditional Formatting or in the formula using IF, define style. Edit your question and with a clip icon post an example of the file.

[MikeMcClain46]

 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 

[sokol92]

Answers to the questions specified in the start message:

' Removes conditional formatting from the oRange 
Sub ClearConFormat(ByVal oRange)
  Dim oConFormat
  oConFormat = oRange.ConditionalFormat
  oConFormat.Clear
  oRange.ConditionalFormat = oConFormat
End Sub

' Set conditional format: if cell value less than RC[-1] then use red text
' Macro taken from A.Pitonyak's book OOME_4_0.odt 
Sub ExConFormat
  Dim oRange, oConFormat
  Dim oCondition(3) As New com.sun.star.beans.PropertyValue
  oRange = ThisComponent.Sheets(0).getCellRangeByName("B1:B5")
  ClearConFormat oRange
  oConFormat = oRange.ConditionalFormat
  oCondition(0).Name = "Operator"
  oCondition(0).Value = com.sun.star.sheet.ConditionOperator.FORMULA
  oCondition(1).Name = "Formula1"
  oCondition(1).Value = "=(B1<A1)"
  oCondition(2).Name = "StyleName"
  oCondition(2).Value = "Bad"
  oCondition(3).Name = "SourcePosition"
  oCondition(3).Value = oRange.getCellByPosition(0,0).getCellAddress
  oConFormat.addNew(oCondition())
  oRange.ConditionalFormat = oConFormat
End Sub

[schiavinatto]

@sokol92

oCondition(1).Value = "=(B1<$A$1)"