is there other faster way of doing this...

Question

below code does the following: it takes a range, then finds distinct values in a range, and stores them in a d_distinct array, also for every distinct value it creates the distinct color, then using the Excel.FormatCondition it colors the range... (my current range is A1:HM232)

for (int t = 0; t < d_distinct.Length; t++ )
{                        
    Excel.FormatCondition cond =
        (Excel.FormatCondition)range.FormatConditions.Add(
        Excel.XlFormatConditionType.xlCellValue,
        Excel.XlFormatConditionOperator.xlEqual, 
        "="+d_distinct[t],
        mis, mis, mis, mis, mis);
    cond.Interior.PatternColorIndex = 
        Excel.Constants.xlAutomatic;
    cond.Interior.TintAndShade = 0;
    cond.Interior.Color = ColorTranslator.ToWin32(c[t]);
    cond.StopIfTrue = false;                        
}

But this works too slow... user will have to sit and wait for about a minute... I did this with this way since, otherwise if I do it with one line of code simply doing this (which colors amazingly fast)

range.FormatConditions.AddColorScale(3);

I will not be able to request the color of the cell... (i can have more than ten distinct values in a range)

can you help me to make my first way work faster? thanks in advance!

Answer 1

Try turning off screen updating while the code is running and turn it back on afterwards. In VBA, that would be:

Application.ScreenUpdating = False
// do stuff
Application.ScreenUpdating = True

As you are not using VBA, try http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel._application.screenupdating(office.11).aspx for guidance

Answer 2

Have you tried using the OpenXML SDK to work with Office 2007? I have used it for word and found it much faster than using vba or COM. Try version 2.0 or 1.0. I think 2.0 is still CTP though. Much more intuitive than vba in my book though. Otherwise, the screenupdating trick is probably the easiest thing to do.