Hi,
I am using the following code in a workbook ..which works fine by the way.
However, in another module of code which has no mention whatsoever of this
function..for some inexplicable reason the code calls this function and runs
through its code completley unnecessairly. This slows down the code in the
other module considerably and I want to resolve this.

As I said there is no mention at all of this function in my code so I have
not included it here. I cannot understand why it should be called. Can anyone
advise on this problem please.






Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function

Hi Alan,

The line:

Application.Volatile True

will update each instance of the function when recalculation occurs.


---
Regards,
Norman



"Alan M" wrote in message
...
Hi,
I am using the following code in a workbook ..which works fine by the way.
However, in another module of code which has no mention whatsoever of this
function..for some inexplicable reason the code calls this function and
runs
through its code completley unnecessairly. This slows down the code in the
other module considerably and I want to resolve this.

As I said there is no mention at all of this function in my code so I have
not included it here. I cannot understand why it should be called. Can
anyone
advise on this problem please.






Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng

End Function