VBA not auto updating cell values

[Chrispykill]

Hi,

I am using a VBA to count cells based on their fill colour. I am using the following script:

Function CountColor(Rng As Range, RngColor As Range) As Integer
Application.Volatile True
Dim Cll As Range
Dim Clr As Long
Clr = RngColor.Range("A1").Interior.Color
For Each Cll In Rng
If Cll.Interior.Color = Clr Then
CountColor = CountColor + 1
End If
Next Cll
End Function

Then in the cell I am using this formula:

=countcolor(B7:Q7,W7)

Where B7:Q7 is the range and W7 the referenced colour.

When changing a cell in the range to the reference colour the only way to get the count value to update is if I double click the cell and press ENTER.

I did have the script without the volatile, with the volatile, and then with the volatile and TRUE at the end. Neither of these worked in auto updating.

Can someone please help?

Thanks!

 

[Roxxien]

There is something that I don't understand and might be a problem.

If you use a 1 cell range for RngColor, why aren't you simply doing this.

Clr=RngColor.interior.Color

 

[Chrispykill]

I don't know hot to write the scripts myself. I only got it from googling what I wanted to do in Excel.

 

[Roxxien]

Change that line

[COLOR=#333333]Clr = RngColor.Range("A1").Interior.Color[/COLOR]

For this

Clr=RngColor.interior.Color

After reading your problem again, it seems that your Excel doesn't automatically recalculate formula.
To activate that, you can go in Options-> Formula and select Automatic of Workbook calculation