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
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
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