Excel VBA Function sum colored Cell count colored Cell

This Excel tutorial explains how to sum colored Cell and count colored Cell in Excel worksheet.

Excel VBA Function sum colored Cell and count colored cell

In my previous posts, I have explained how to use ColorIndex Property to find the first colored Cell. In this Post I will create a custom Function to count colored Cell of a Range and sum colored Cell

To recap the previous posts, you can click on the followings.

Excel VBA custom Function Find the first colored Cell value

VBA Excel ColorIndex Property

VBA Function Code – count colored Cell using ColorIndex Property

In the below Function, I use ColorIndex<>xlNone to identify colored Cell. White is deemed as colored, while “No Fill” is not colored.

If you just want to sum or count specific color, change the code

Interior.ColorIndex <> xlNone to Interior.ColorIndex = color_index (click here to see the list of color index)

Public Function wCountColorCell(rng As Range)
    Application.Volatile
    wCountColorCell = 0
    For Each r In rng
        If r.Interior.ColorIndex <> xlNone Then
            wCountColorCell = wCountColorCell+1
        End If
    Next r
End Function

Function Syntax – count colored Cell value using ColorIndex Property

wCountColorCell(rng)

rng is a Range that contains one more or Cells.

Function Example – count colored Cell value ColorIndex Property

Once you type a formula in Cell, the result cannot be refreshed automatically if you add/remove color to argument Range.

Formula	Result	Explanation
=wCountColorCell(A2:C2)	2	Count color Cell in the same row
=wCountColorCell(A2:A8)	2	Count color Cell in the same column
=wCountColorCell(A3:C8)	2	Count color Cell in the across different column and row

VBA Function Code – sum colored Cell using ColorIndex Property

In the below Function, I use ColorIndex<>xlNone to identify colored Cell. White is deemed as colored, while “No Fill” is not colored.

If you just want to sum or count specific color, change the code

Interior.ColorIndex <> xlNone to Interior.ColorIndex = color_index (click here to see the list of color index)

Public Function wSumColorCell(rng As Range)
    Application.Volatile
    wSumColorCell = 0
    For Each r In rng
        If r.Interior.ColorIndex <> xlNone Then
            wSumColorCell = wSumColorCell+r.Value
        End If
    Next r
End Function

Function Example – sum colored Cell using ColorIndex Property

Once you type a formula in Cell, the result cannot be refreshed automatically if you add/remove color to argument Range.

Formula	Result	Explanation
=wSumColorCell(A2:C2)	4	Sum color Cell in the same row
=wSumColorCell(A2:A8)	20	Sum color Cell in the same column
=wSumColorCell(A3:C8)	27	Sum color Cell in the across different column and row

 

Excel VBA Function sum colored Cell count colored Cell