Recalulation of functions

  • Thread starter Thread starter Carl Brehm
  • Start date Start date
C

Carl Brehm

In about 1000 cells I have

=IF((CHECKCOLOR('On Hand'!B2)-'On Hand'!B2)<=0,"",CHECKCOLOR('On
Hand'!B2)-'On Hand'!B2)

Why is it that when I change the colorindex of a cell a recalculation is
not preformed. In fact when I try to force one with F9 or shift-F9 it still
does not recalculate? If I enter the edit mode of the formula and exit it
works.


Function CHECKCOLOR(CL As Range) As Integer

Dim NUM As Integer

NUM = CL.Interior.ColorIndex

Select Case NUM
Case 6
CHECKCOLOR = 1 'YELLOW
Case 5
CHECKCOLOR = 2 'BLUE
Case 46
CHECKCOLOR = 0 'RED
Case Else
CHECKCOLOR = 0
End Select

End Function

=IF((CHECKCOLOR('On Hand'!B2)-'On Hand'!B2)<=0,"",CHECKCOLOR('On
Hand'!B2)-'On Hand'!B2)

--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Cages
 
Unfortunately, changing the ColorIndex of a cell does not fire an event that
is recognized by VBA.
 
Vasant Nanavati



Microsoft Excel automatically recalculates formulas when the value
depend on cells that have changed.

no calculation is performed As the cell value has not change
 
Hi Charles,

Application.Volatile won't help in this case (where only a format is
changed)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
Sorry Charles,

I didn't read well enough and overlooked the "then use F9"
Of course it works!
 
Back
Top