(VBA) Refresh cell if another cell in a range changes colour

[KVexcel]

So I have this function that counts cells of a color on a range

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

I want the function cell to be refreshed each time a cell on the given range changes colour. Also, is there a way to incorporate this inside the function code or does it have to be a seperate sub ?

 

[Fluff]

There is no way to do that as there is no-way to pick-up the fact that a cell has had the colour changed. The best you could do would be to use a selection change event, but for that to work you would need to select another cell after changing the colour.

 

[KVexcel]

I use a macro recorder to insert the data in another program, and it makes the adress yellow as a last step so I can have a visual representation that it has finished with this row. So it selects other cells all the time after changing colours.

TAGS TEMPLATE.xlsm
	A	B	C
1	TAG NAME	ADRESS	TOTAL TAG COUNT:
2	AC CONTROL PANEL COMMON ALARM	1865	1074
3	ACH_AC POWER FAILURE	1041
4	ACH_AH DRUM CLUTCH IN	1062	READY TAGS:
5	ACH_AH DRUM CLUTCH OUT	1063	2
6	ACH_AH DRUM HOLD	1060	0.343488494
EASY TAGS

Cell Formulas
Range	Formula
A2:B1075	A2	=SORT(FILTER(calc!$A2:$B5001, (calc!$A2:$A5001<>"")+(calc!$B2:$B5001<>"")))
C2	C2	=5000-COUNTBLANK($A2:$A5001)
C5	C5	=GetColorCount(B2:B5001,$B$5002)
C6	C6	=RAND()
Dynamic array formulas.

C6 is there just to check visually if C5 is getting refreshed, I tried with

Sub Calculate_range()
    Worksheets("EASY TAGS").Range("C5:C6").Calculate
    Application.OnTime DateAdd("s", 1, Now), "Calculate_range"
End Sub

but unfortunately it didn't work

 

[Fluff]

Are the cells being coloured manually, or by a macro?

 

[KVexcel]

By a macro, but it has mouse tracking inside the loop, so it actually clicks on the fill colour button on excel.
It's not an excel macro.

 

[Fluff]

In that case after you have run the macro, why not run another that will update the count?

 

[KVexcel]

The mouse & keyboard macro does Alt-Tab to other applications, and it takes ~4,5seconds per row. With my datasets (up to 3000 rows) it takes quite a while to finish, and sometimes I may need to stop it and resume later. So i would like the count te be refreshed constantly.
I searched the forum based on your first answer and found this post where you also commented
Selection Change event
Maybe I need something like this ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rPrintArea As Range

 If Not Intersect(Target, Range("B2:B5001")) Is Nothing Then
    Worksheets("EASY TAGS").Range("C5:C6").Calculate
 End If
 
 End Sub

 

[Fluff]

That should do it.

 

[KVexcel]

I tried and it didn't work. This should refresh C5:C6 whenever you select cells on the B2:B5001 range, right?

 

[Fluff]

You will also need to make the UDF volatile like

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
Application.Volatile
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function