Hi,
I use delphi to manipulate excel and frequently format cells iteratively. I am told that using say range['A1'].select and then excel.selection.value:= etc is time consuming and the best way is to put just range['A1'].value:= as the select process takes time.  So, I have the following code which I wish to rif of the .select

  for i := 0 to NumCodesMPSDetail-1 do
  begin
    Num1 := (13*i)+10;
    WS.range['B'+inttostr(Num1+1)+':M'+inttostr(Num1+1)].select;
    Excel.Selection.FormatConditions.Delete;
    Excel.Selection.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4));
    Excel.Selection.FormatConditions[1].Interior.ColorIndex := 7;
    Excel.Selection.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
    Excel.Selection.FormatConditions[2].Interior.ColorIndex := 3;
  end;
 
I have put
  for i := 0 to NumCodesMPSDetail-1 do
  begin
    Num1 := (13*i)+10;
    RangeString := 'B'+inttostr(Num1+1)+':M'+inttostr(Num1+1);
    Ranger := ws.range[rangestring];
    ranger.FormatConditions.Delete;
    ranger.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4)); //note put a but gives b...!
    ranger.FormatConditions[1].Interior.ColorIndex := 7;
    ranger.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
    ranger.FormatConditions[2].Interior.ColorIndex := 3;
  end;  

where ranger is a variant.  This works of sorts.  Where I have put '=b$', my formatcondition actually comes out as '=a$' within the cells in excel.  So I have 2 questions:
1. Why does the column reference b get transposed to a when I get rid of the select?
2. How can I use the with statement instead of having to write ranger. each time.  ie. with ranger do begin formatconditions.delete; etc end;
Thanks

Everything looks fine without running the actual code.

Are you early or late binding?  Have you tried selecting the whole range of cells and then apply the formatting to them so you're down to a few 'select' calls?

Have you turned off screen updates in Excel while you're processing the document?

Hi,

I am late binding.  I have not turned off screen updates but I will.  I cannot select the whole range of cells as it's only every 13th row of 5000 rows that I want the 2 conditional formatting lines on, so it's row 10,23,36 etc (ie 10+x*i).
Also I cannot use the following scenario.  I have even tried putting ws.pagesetup as a variant.
  ws.pagesetup.centerheader := 'This works';
  with ws.pagesetup do
  begin
    CenterHeader := 'This does not';
  end;
I will put this as a separate thread.  
Thanks