create macro for Conditional Format

[ypkdani]

Hello,

i have a problem to create a Conditional Format using macro in a group of cells. My code is:

Blockquote

Dim nRiga As Integer
Dim index As Integer
Dim value As Double
Dim checkResult As Integer
Dim func As String

Dim oRange        'Cell range to use
Dim oConFormat    'Conditional format object
Dim oRedCondition(3) As New com.sun.star.beans.PropertyValue
Dim oGreenCondition(4) As New com.sun.star.beans.PropertyValue

	
checkResult = 0

nRiga = startRow

REM Sheets support returning a cell range based on UI type names.
oDoc = ThisComponent
oSheets = oDoc.getSheets()
sheet = oSheets.getByName(sheetName)
oRange = sheet.getCellRangeByPosition(13, nRiga+1, 13, nRiga+9)

REM Obtain the Validation object
oConFormat = oRange.ConditionalFormat

oRedCondition(0).Name = "Operator"
oRedCondition(0).Value = com.sun.star.sheet.ConditionOperator.NOT_BETWEEN
oRedCondition(1).Name = "Formula1"
oRedCondition(1).Value = CStr(testLimit)
oRedCondition(2).Name = "Formula2"
oRedCondition(2).Value = CStr(-testLimit)
oRedCondition(3).Name = "StyleName"
oRedCondition(3).Value = "Senza nome1"
oConFormat.addNew(oRedCondition())
oRange.ConditionalFormat = oConFormat

Blockquote

I need to find the value less or upper the value 0.1. The macro dosn’t work fine, if the cell value is 0 it works correct but if the value is 0.05 and the testlimit 0.1 it color the cell with red. If i enter to the manage Conditional Format, press modify on it and the ok (without change value) this begin to work fine. In this cell there is a formula.

Why there is this problem?

Thanks

[Lupp]

You sure the variables nRiga and testLimit actually had the intended values?
If I assign applicable values to nRiga and to testLimit the code works for me as expected.
The StyleName “Senza nome1” mus exist as a cell style and be adequately defined, of course, in addition.

[ypkdani]

Hello,

yes the StyleName exist and work fine as the TestLimit that have a value of 0,1 and nRiga are right.
If i have for example:

L116 = 45 
M116 = 45 

the result is correct, no color
but if i have:

L116 = 45 
M116 = 45.01 

the result is incorrect, red color. That is wrong.

Thanks

[Lupp]

My suspicion is you did not try to see the CF in the CF manager. There you find either zeros or texts in both number fields intended to be used here for lower and upper limit.
The suspected reason: The field contents must be passed as strings. You therefore call the Cstr function. But you probably found a way to pass 0,1 and -0,1 to the CF… Locale decimal separator is disregarded in BASIC (fortunately).
Please post next time the complete code icluding relevant assignments.

[librebel]

Hello @ypkdani,

try if it works if you call oConFormat.clear() just before oConFormat.addNew().

[ypkdani]

Tried but nothing

[PYS]

Hi

I reproduce the problem in my french environnment (decimal separator: comma). But, I do not reproduce the problem if I use:

oRange.ConditionalFormatLocal = oConFormat

Also I prefer to use:

oRedCondition(1).Name = "Formula1"
oRedCondition(1).Value = cstr(-testLimit)
oRedCondition(2).Name = "Formula2"
oRedCondition(2).Value = cstr(testLimit)

Regards