Hi

Then you have the calculation set to manual. Depending on your Excel
version, you can check it:
a) Excel 2000: ToolsOptionsCalculations - Calculation radio button must be
set to Automatic;
b) Excel 2007: Office ButtonExcel OptionsFormulas - Workbook Calculation
radio button must be set to Automatic.


--
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