Hi Alan,

Not sure what you are using for code in VSTO but whatever it is changing
cell formats individually is slow. One way to speed up is to colour the
entire range to suit the condition you expect most of the cells to meet.
Then ignore changing these individually in the loop. In VBA, and similar in
VB6 with additional ref to Excel, something like this:

Sub Test()
Dim i As Long, j As Long
Dim rng As Range
Dim wksheet As Worksheet
Dim vals
Dim XXX

Set wksheet = ActiveSheet
Set rng = wksheet.Range("A1:Z1000")

rng.Rows(3).Value = 1
rng.Rows(4).Value = -1

vals = rng.Value

XXX = 0

rng.Interior.ColorIndex = 2

For i = 1 To UBound(vals)
For j = 1 To UBound(vals, 2)
If vals(i, j) XXX Then
rng(i, j).Interior.ColorIndex = 1
ElseIf vals(i, j) < XXX Then
rng(i, j).Interior.ColorIndex = 3
End If
Next
Next

End Sub

iso of colorindex = 2 (white), maybe use xlAutomatic (-4142)

Regards,
Peter T

"Alan" wrote in message
...
Hi all,

Can anybody help clarifying my issues?

In one part of my Excel/VSTO application, I have to compare values of
cells
and assign a background color according to the result of the comparison.
The code looks something like this:

...
rng = wkSheet.Range("A1:Z1000")
vals = Array(1000 * 26 ) ' same size as 'rng'

Do ( for the whole range )
If vals(i, j) XXX Then
rng(i, j).Interior.ColorIndex = 1
Else If vals(i, j) = XXX Then
rng(i, j).Interior.ColorIndex = 2
Else ' vals(i, j) < XXX Then
rng(i, j).Interior.ColorIndex = 3
End If
Loop

It takes 10 seconds on my machine for excel to complete the loop, i.e.
excel will stop responding for 10 secs:
this is not acceptable for my app.

I tried:
- ThisApplication.ScreenUpdating = False
- ThisApplication.Calculation = Excel.XlCalculation.xlCalculationManual
they don't help much.


*** I don't want to use "Conditional formatting" for security reason.


To my understanding, for every change/call to a member/method of a Range
object, there is one out-of-process communication
to the Excel COM Server, correct? (sorry if I used wrong technical terms
about COM; not a COM fan )

And this out-of-process communication is slow; I have 1000*26 calls in my
do
loop, so it's (slow*26000) = damn slow :-)

My idea is to have a local copy (variable) of a Range object, then make
some
changes and later notify the Excel COM Server of the change just once,
something like:

...
rngNotAsACOMObj =
(ISomeInterfaceThatDoesTheTrick)wkSheet.Range("A1: Z1000")

rngNotAsACOMObj.NoConectionToCOMServer()
rngNotAsACOMObj.Interior.Color = 10 <=== Excel doesn't see the change yet.
rngNotAsACOMObj.Interior.ColorIndex = 4 <=== Still not.
rngNotAsACOMObj.NotifyChange() <=== Excel sheet gets updated.

Is this possible at all?
Any better solution exist?


Cheers -- Alan