Hello. I am writing a program that inserts strings into a template xlsx. The template uses conditional formatting with references to strings that shift when my strings are added. For example, the conditional formatting formula in the template is: I5=$I$18-$J$18. The range of this conditional formatting in the template is: $I$5:$I$6. When this condition is met, cells I5 and I6 are colored.
Next, I add the lines 6,7,8...N (between 5 and 18) and wish the conditional formatting to extend to them. I found a workaround to change the range
Pranges:PsCellRange;
currentFormat:TsConditionalFormat;
currentFormat:=wbook.GetConditionalFormat(i);
Pranges:=@currentFormat.CellRange;
Pranges^:=targetRange;
StoreCFIndexInCells(wsheet,i,targetRange);
StoreCFIndexInCells is a copy of your non-public method from fpspreadsheet_cf.inc
However, the text of the conditional formatting formula remains the same and the condition does not work. I would like to get I5=$I$M-$J$M, where M is the new index.
Are there any methods by which I could implement offsets of references to offset cells?