Using function based on "Font Color"

[danai_mp]

Could you please inform me if there is a function which can count and/or
summarize cells in excel based on "Font Color"

 

[Bob Phillips]

Function CountCells(rng As Range, ci As Long)

For each cell in rng

If cell.Font.Colorindex = ci then

CountCells = CountCells +1
End If
Next cell
End Function

=CountCells(A1:B10,3)

 

[danai_mp]

Thank you very much for your answer but unfortunately I am not so good in this
I need more help
If I have A1 = 7 (red colour), A2 =5 (green colour), A3 = 4 (lime colour),
A4 = 2 (green colour), A5 =6 (red colour), A6 =2 (red colour) etc
what can I use to have (a) the sum of red colour cells and (b) the count of
green colour cells?
thank you
--
Best regards
danai_mp


Ο χÏήστης "Bob Phillips" έγγÏαψε:

 

[Bob Phillips]

Here is a slightly different way.

Add this function

Function CellColours(rng As Range) As Variant
Dim ary As Variant
Dim cell As Range
Dim i As Long

ReDim ary(1 To rng.Rows.Count)
i = 1
For Each cell In rng

ary(i) = cell.Font.ColorIndex
i = i + 1
Next cell
CellColours = ary
End Function

and then sum like

=SUMPRODUCT(--(CellColours(A1:A10)=3) ,A1:A10)

and simply count like

=SUMPRODUCT(--(CellColours(A1:A10)=10))