Delphi and Excel.FormatConditions

Question

I'm having trouble setting up conditional formatting from Delphi XE2 using early binding with Excel 2010

The macro I'm trying to reproduce is as follows:

Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="=6" Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .ThemeColor = xlThemeColorAccent6 .TintAndShade = 0 End With Selection.FormatConditions(1).StopIfTrue = False 

Try so that I can not access the Selction.FormatConditions(1) equivalent to work

The closest I came to has the following code:

 XR := Xlapp.Range(...) XR.FormatConditions.Delete; XR.FormatConditions.Add(xlCellValue, xlGreater, '=6', EmptyParam, EmptyParam, EmptyParam, EmptyParam, EmptyParam); 

What works. When I try to identify colors I have problems

 FC := XR.FormatConditions[1]; FC.SetFirstPriority; with FC.Interior do begin PatternColorIndex := xlAutomatic; ThemeColor := xlThemeColorAccent6; end; 

However, this continues to tell me that XR.FormatConditions (1) is also an IDispatch, and therefore incompatible with the purpose of FormatCondition

What am I doing wrong?

Answer 1

You need to use Selection as ExcelRange . Excel XP also requires that the second and third parameters be OleVariant , so this should work (it compiles, anyway):

 var Sel: ExcelRange; Op, Formula: OleVariant; Condition: FormatCondition; begin Sel := ExcelApplication1.Selection[1] as ExcelRange; Op := xlGreater; Formula := '=6'; Sel.FormatConditions.Add(xlCellValue, Op, Formula, EmptyParam); Condition := Sel.FormatConditions[1] as FormatCondition; Condition.Interior.PatternColorIndex := xlAutomatic; // Do whatever else end;