Counting cells by the fill colour 1

[Skyshadow5]

Having looked at previous posts, I have found (most of) an answer to my query, however, there is one part I could not find.
I have added the following VBA script to a new module in Excel 2000 (thanks to previous post)
===================================================
Function CountByColor(CountRange As Range, Color As Integer) As Long
Dim c As Range, ColorCount As Long
For Each c In CountRange
If c.Interior.ColorIndex = Color Then
ColorCount = ColorCount + 1
End If
Next
CountByColor = ColorCount
End Function
===================================================
and when using the formula =COUNTBYCOLOR(namedrange,colorindexnumber) I do get the correct figure. However, if I then proceed to fill more cells with a colour, the formula does not automatically add in the additional coloured cells. I have tried Ctl+Alt+F9 to force a recalculation but this does not work. If I doubleclick the cell and hit enter, this works, but there are over 50 cells with combinations of this formula and I do not want the user to have to manually re-do each one.
Is there any way of having these formulae automatically account for more cells that get filled?
TIA
Clive Reeves

 

[GlennUK]

If you put the statement

Application.Volatile True

after your Dim statemenr then the couting will occur every time the sheet recalculates. ( Note: filling a cell colour does not trigger recalculation ).


Cheers, Glenn.

 

[Skyshadow5]

Cheers Glenn, I had read this but was dubious about using it as I read (somewhere) that this could impact the performance of Excel.
I added it in and found no impact on performance so I guess this only applies if you're running an old slow machine or a spreadsheet with huge numbers of cell entries / formulae.
Thanks for the advice, a quick stab at the F9 key after adding and hey presto.

Regards
Clive

 

[GlennUK]

My pleasure

Cheers, Glenn.