Multi-selection address in range field of CF with macro

[rammy]

In the cell range of conditionalformat, Format.Conditional.Manage.Edit, I can give different ranges by hand, example “A1:D1,A2:E2,A3:H3”

When I use the folowing code all the rules are separated over more lines:

sRngs = "A1:D1,A2:E2,A3:H3"
vRngs = split(sRngs, ",")
for iI = lbound(vRngs()) to ubound(vRngs())
  oRng.getCellRangeByName(vRngs(iI)).ConditionalFormat = oNewCCF               'each on a separated line
next

How to change the code so that all ranges are on one cell range line only?
I tryed it with:

oRng.getCellRangeByName(sRngs).ConditionalFormat = oNewCCF 'All on one line

But thats not the solution.

Who can/will point me in the rigth direction?

Regards.

Rammy

[sokol92]

' Changes the range of cells for the first conditional formatting of the first sheet of the document.
Sub ChangeCFRanges()
  Dim oSheet as Object, oRanges As Object, oCF As Object, n
  
  oSheet=ThisComponent.Sheets(0)
  oRanges=Thiscomponent.createInstance("com.sun.star.sheet.SheetCellRanges") 
  For Each n In Split("A1:D1;A2:E2;A3:H3", ";")
    oRanges.addRangeAddress oSheet.GetCellRangeByName(n).rangeAddress, True
  Next n
    
  oCF=oSheet.ConditionalFormats.ConditionalFormats(0)
  oCF.range=oRanges
End Sub

[rammy]

I have adopted your code in the following way:

Dim oCF As Object, sRngs As String
sRngs = "A1:D1;A2:E2;A3:H3"
  
'oSheet = ThisComponent.Sheets(0)
oSheet = oSheets.getByIndex(oRng.RangeAddress.sheet)
For Each sRng In Split(sRngs, ";")
   oRngTmp.addRangeAddress oSheet.GetCellRangeByName(sRng).rangeAddress, True
Next sRng
msgbox "'" & oRngTmp.absoluteName & "'"
'oCF = oSheet.ConditionalFormats.ConditionalFormats(0)
oCF = oSheet.ConditionalFormats.ConditionalFormats(oRng.RangeAddress.sheet)
oCF.range = oRngTmp

The variable oRngTmp contains the addresses: “$Eltako.$A$1:D1;$Eltako.$A$2:$E$2;$Eltako.$A$3:$H$3”
An error, ‘Inadmissible value or data type. Index out of define range’, is produced by line:
oCF = oSheet.ConditionalFormats.ConditionalFormats(oRng.RangeAddress.sheet)

What is cause of this error and how to solve it?

[sokol92]

Could you upload the .ods file along with the macro? This way we will solve the problem faster.


Please note that the macro does not create a new formatting rule, but changes an existing one (the first one).

[rammy]

CFExample_2.ods (198.2 KB)

Of course is that possible, see file.
When the new CF rules are created the cell(s) contains no rules at all.

[sokol92]

Error in line:

oCF = oSheet.ConditionalFormats.ConditionalFormats(oRng.RangeAddress.sheet)

The last index should be equal to 0 - this is the index of the conditional formatting rule (this is the only rule on the sheet).

[rammy]

I change the line:

oCF = oSheet.ConditionalFormats.ConditionalFormats(oRng.RangeAddress.sheet)

back to:

oCF = oSheet.ConditionalFormats.ConditionalFormats(0)

It works when the cell already contains CF rule(s) but when I remove the rule(s) or take a complete new empty sheet I get the same error, ‘Inadmissible value or data type. Index out of define range’
Is there a way to make it also work on cells that contains NO CF rule(s)?

[sokol92]

The workaround I use is to create a CF for one rectangular range and then change the CF area using the above method. You can also look for the “direct” path - let us know if you succeed.

[rammy]

I will search for the “direct” path, meanwhile I will use the workaround.
Thanks for spending your time and sharing your knowledge with me (us).