Count the colour cells

[Basiliadmonte]

I have filled in colour in certain cells of the excel sheet.If now i have to count the number of cells filled What I have to do???? I m trying a number of things but yet unable to do it .......................PLease help

A
1 Y
2 -
3 -
4 Y
5 -
6 -
Now what is the formula to be used for this where the "Y' cells are colored as red?

 

[Akashwani]

Hi and welcome to MrExcel.

Try this solution by MrExcel himself.....

Excel Counting cells by interior color using VBA

Or this (post #2) by another Excel great.......

http://www.mrexcel.com/forum/excel-...applications-count-items-cell-color-font.html

I hope that helps.

Ak

 

[shg]

Color is not data to Excel. The formula will not update automatically if you change the color of a cell.

 

[Gigi_]

HI,
insert this code in VBA

Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long


Dim Rng As Range
Application.Volatile True


For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
End If
Next Rng


End Function

and the use this formula on your sheet =COUNTBYCOLOR(A1:A15;3;VERO)
(maybe you'll must change VERO with TRUE, i'm not sure, i'm italian and i'm using italian ms office)