Custom vba formula within a countif statement

Question

I have a custom formula which returns a colour code based on the background colour of a cell.

    Function GetFillColor(Rng As Range) As Long
    GetFillColor = Rng.Interior.ColorIndex
End Function

The below works fine and returns 15 (which is the colour code of my cell).

=GetFillColor(AD3)

This works within in 'if' function if I use the function for a single cell for example and it returns 1.

=IF(GetFillColor(AD3)=15,1,0)

However if I try to reference within a countif function it doesnt work.

=COUNTIF(AC3:AD3,GetFillColor()=15)

I'm not sure how to reference the range within the function.

CDP1802 · Accepted Answer · 2025-06-13 15:52:50Z

3

Function CountFillColor(rng As Range, i As Long) As Long
    Dim c As Range, n As Long
    For Each c In rng
       If c.Interior.ColorIndex = i Then n = n + 1
    Next
    CountFillColor = n
End Function

answered Jun 13 at 15:52

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

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

Comments

CHill60 · Accepted Answer · 2025-06-13 15:17:46Z

2

You can't pass a multiple cell range to this function and get a meaningful result

You could have another function that iterates through the range, calling GetFillColor() for each cell, or, easier, have helper columns that "calculate" the fill colour with formula e.g. =GetFillColor(AC3) and do the countif on those

answered Jun 13 at 15:17

CHill60

2,0711 gold badge14 silver badges22 bronze badges

Comments

Tim Williams · Accepted Answer · 2025-06-13 16:40:06Z

2

You can modify your formula to work in both single- and multi-cell contexts:

Function GetFillColor(Rng As Range)
    Dim res, r As Long, c As Long, ubr As Long, ubc As Long
    ubr = Rng.Rows.Count
    ubc = Rng.Columns.Count
    ReDim res(1 To ubr, 1 To ubc)
    For r = 1 To ubr
        For c = 1 To ubc
            res(r, c) = Rng.Cells(r, c).Interior.ColorIndex
        Next c
    Next r
    If ubr = 1 And ubc = 1 Then
        GetFillColor = res(1, 1)
    Else
        GetFillColor = res
    End If
End Function

If given a single cell it will return a scalar value, but for multi-cell ranges it will return a 2D array of color indexes.

Eg: array formula use

=SUM(1*(GetFillColor(AC3:AD3)=15))

answered Jun 13 at 16:40

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Comments

Promethee · Accepted Answer · 2025-06-15 10:26:44Z

2

Alternatively in modern Excel, you can keep the VBA function as is and rely on Excel function MAP:

=SUM(1 * (MAP(AC3:AD3; LAMBDA(MyCell; GetFillColor(MyCell))) = 15))

answered Jun 15 at 10:26

Promethee

1166 bronze badges

Comments

Black cat · Accepted Answer · 2025-06-14 06:06:18Z

What you want to achieve the formula syntax should be:

=COUNTIF(GetFillColor(G3:N3),15)

but this will not work since COUNTIF function not accepts array as the first parameter.

You need to modify your function to return an array, and apply some other functions to calculate the result.
The below code is a mixed version of CDP1802 and Tim Williams.

 Function GetFillColor(Rng As Range) As Long()
    ReDim coll(0 To Rng.Cells.Count - 1) As Long   'should be the same type as in the Function declaration
    For Each cel In Rng
    coll(i) = cel.Interior.ColorIndex
    i = i + 1
    Next cel
    GetFillColor = coll
End Function

and the usage of it can be:

=SUM(IF(GetFillColor(G3:N3)=15,1,0))

or

=SUMPRODUCT(--(GetFillColor(G3:N3)=15))

or one of the other answers formulas.

Collectives™ on Stack Overflow

Custom vba formula within a countif statement

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related