Author: Oscar Cronquist Article last updated on October 08, 2018


i need to count unique number in a specific coloured cell
for eg
if there are coloured cells like red yellow green
and in want to know unique number in red cell....

count unique distinct values by cell color

The following custom function counts unique distinct values in a cell range by color.

Formula in cell D3:


The first argument is the cell range. The second argument is a single cell with a color you want to use as a condition. Cell C3 has the interior color red.

VBA code

Function CountC(rng As Range, Cell As Range)
Dim CellC As Range, ucoll As New Collection
For Each CellC In rng
    If CellC.Interior.Color = Cell.Interior.Color Then
        On Error Resume Next
            If Len(CellC) > 0 Then ucoll.Add CellC, CStr(CellC)
        On Error GoTo 0
    End If
Next CellC
CountC = ucoll.Count
End Function

Download excel *.xlsm

Count unique distinct values in colored cells.xlsm