CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
user4584837
user4584837

Reputation: 21

Excel Countifs formula with one color criteria and the rest normal

How do I get an excel 2010 formula that can do countifs criteria that includes color? I saw this: https://support.microsoft.com/en-us/kb/2815384 but it only gives me the count of cells with that color. I need an additional criteria to be available in the formula or I need the VBA that is provided in the link there to only work on visible cells (using filters on my data). Any ideas? I appreciate the help.

Here is the code provided by the link:

Function CountCcolor(range_data As range, criteria As range) As Long
    Dim datax As range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function

Upvotes: 1

Views: 2812

Answers (2)

Andrew Weatherly
Andrew Weatherly

Reputation: 178

So is your issue that you don't want filtered data to be found when you apply the custom function? Because if so, the following should fix it:

Function CountCcolor(range_data As range, criteria As range) As Long
    Dim datax As range
    Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
    If datax.Interior.ColorIndex = xcolor And datax.EntireRow.Hidden = False Then
        CountCcolor = CountCcolor + 1
    End If
Next datax
End Function

Upvotes: 1

user1274820
user1274820

Reputation: 8144

Andrew Weatherly's answer is just about what you're looking for.

The only thing I had issues with when running his code is this line:

if datax.Hidden = false

This becomes datax.EntireRow.Hidden = False and that seems to fix things.

It also doesn't like to update when it should.

In doing some poking around, I added Application.Volatile, but it doesn't seem to update when the color changes or when the filter changes.

I was forced to add a Worksheet SelectionChange event that finally got it updating when it was supposed to (probably too often).

Excel doesn't have a "color change" event, so the best we can do is update when the selection changes (short of adding a timer that runs Application.Calculate, but that sounds like more effort than it's worth).

The final code:

Function CountColors(TheRange As Range, TheColor As Range) As Long
Application.Volatile
Dim c, color, cellcount
color = TheColor.Interior.ColorIndex
For Each c In TheRange
    If c.EntireRow.Hidden = False And c.Interior.ColorIndex = color Then cellcount = cellcount + 1
Next c
CountColors = cellcount
End Function

Sheet1 Event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

The first piece of code goes in a module (Module1 is the default).

The second piece of code goes in your worksheet object (Sheet1 (Sheet1) in my example).

CountColors

Upvotes: 1

Related Questions