An explanation how this IF(NOW()=NOW(),...) works.

Functions may be volatile or not volatile. Volatile is a function, which
starts formual calculation with any cell value changing (P.e. if you have
in some cell a formula =NOW(), then recalaculation is started every time you
edit any cell). Non-volatile starts recalculation only, when any of it's
parameters has changed (P.e. if you have in some cell a formula =A1, and
there are no other formulas, the recalculation is started only when you edit
cell A1, or the cell with formula).

I didn't check out the code for this ColorIndex function, but as making
functions volatile increases considerably the amount calculation time, I
assumed that ColorIndex() is non-volatile. The construction I adviced, makes
the formula volatile.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Thanks David

Unfortunately it has the same effect as the original formula.

It woll only make the calculation on double clicking in the3 Total cell!

Rob

"David Biddulph" wrote:

If YourFormula is =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14
then Arvi is suggesting that you try
=IF(NOW()=NOW(),SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14)
--
David Biddulph

"hollies" wrote in message
...
Thanks Arvi

However, I am sorry but I do not understand quite what you mean and
what
to
put as the formula!



"Arvi Laanemets" wrote:

Hi

Changing background itself doesn't force recalculation (at least in
Excel2000). Try this:
=IF(NOW()=NOW(),YourFormula)


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"hollies" wrote in message
...
Worked fine but.....

If I remove the red background or add more cells to the "red" list,
the
calculations do not change, the total stay as it was.

eg =SUMPRODUCT(--(ColorIndex(B9:U11)=3))*14 returns the value 140
if
I
make the background RED for the first 10 cells.

If I then change the background of any of these cells by removing
the
RED
or
if I add additional "RED" cells, the total value is not changing.

Again, am I missing something here.

Thanks