In conditional formatting (in Calc) there are the properties .Formula1 and .SourcePosition.

Is there a simple way to convert such formulas to different cell references?

Practically speaking, a function to which I can pass the parameters:

-position of the current cell

-Formula (Formula1 in the conditional format)

-SourcePosition (of the conditional formatting)

and which then returns the current formula.

Let me give the following example to explain this:

1. I select cells D10:D15 (so D15 is the active cell of the selection)

2. I call the Format Conditional Formatting dialog and specify the following formatting:

3. I insert a row and a column (marked pale yellow in the screenshot) and then mark E11 and call Format Conditional Formatting again and see

(All correct.)

If I now read out the conditional formatting from E11:

- Code: Select all Expand viewCollapse view
`With ThisComponent.Sheets.getByIndex(2).GetCellRangeByName("E11").ConditionalFormat(0)`

Msgbox "Formula1: " & .Formula1 & CHR(13) & _

"Operator: " & .Operator & CHR(13) & _

"Source-Sheet: " & .SourcePosition.Sheet & CHR(13) & _

"Source-Column: " & .SourcePosition.Column & CHR(13) & _

"Source-Row: " & .SourcePosition.Row & CHR(13)

End With

I get:

But how can I calculate (by macro) the formula "IF(SUM(G8;H16;E19;K9;myNamendRange)=12)" from the existing values (.Formula1, .SourcePosition)?

I don't mean how I can calculate the value of the formula, I mean how I can get the formula itself (as string)

Please keep in mind: the formulas can be much more complex and it might be difficult to do the conversion in a way that all parts to be converted (i.e. cell names) are separated, then converted and the formula is reassembled. (imho)

Greetings,

joesch