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?

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
– Hans En, Commented Jul 18, 2016 at 7:44

Shai Rado · Accepted Answer · 2016-07-18 07:44:12Z

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, 2016 at 7:44

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hans En Over a year ago

Hi thanks for your answer. Thsi event is not called when I fill a cell. Only when I remove the fill.

user1016274 · Accepted Answer · 2016-07-18 10:06:11Z

0

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.

answered Jul 18, 2016 at 10:06

user1016274

4,2992 gold badges25 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related