How to count all cells with any background coloring using VBA?

Question

I am trying to simply count all cells in a column that have a background color. Any color it doesn´t matter which one. I have used a module using this code:

Function CountCcolor(range_data As Range) As Long
    Dim datax As Range
    For Each datax In range_data
        If datax.Interior.ColorIndex <> xlNone Then
            CountCcolor = CountCcolor + 1
        End If
    Next datax
End Function

This works good when I apply the formula for the first time. It no longer reacts, when I change fill in the sheet. How can I exceute this function, every time the users edit a cell?

Have a look [here](http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change) and [here](http://stackoverflow.com/questions/15337008/excel-vba-run-macro-automatically-whenever-a-cell-is-changed) — lokusking, Jul 18 '16 at 07:34
Hi. thanks I saw that but I can´t get it working. I have only a module with that bit of code in it. In the Worksheet I call it like this =CountCcolor(B5:B18) which works fine. But if i fill new cells it doesn´t react. Sorry I´m new to VBA.. — Hans En, Jul 18 '16 at 07:44

score 0 · Answer 1 · answered Jul 18 '16 at 07:44

0

You can refresh the entire workbook by using the following code, in the relevant Worksheet_Change event :

Private Sub Worksheet_Change(ByVal Target As Range)

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

End Sub

answered Jul 18 '16 at 07:44

Shai Rado

33,032
6
29
51

Hi thanks for your answer. Thsi event is not called when I fill a cell. Only when I remove the fill. – Hans En Jul 18 '16 at 08:04

score 0 · Answer 2 · answered Jul 18 '16 at 10:06

There is no event which is triggered by filling a cell. Thus, I suggest the following worksheet macro

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Sh.Calculate
End Sub

in addition to your macro. Reasoning: after filling a range you will (eventually) select other cells on that sheet. At this moment, the sum is recalculated. It is not ideal as it's not updated while you're selecting the range, and it might be computationally intensive.

How to count all cells with any background coloring using VBA?

2 Answers2