Count formated cells

  • Thread starter Thread starter bobtracey
  • Start date Start date
B

bobtracey

I would like to count the number of cells in a column that have been
formatted a certain colour. is this possible?
 
Bob,

It is using a User-Defined-Function:

Function CountFormat(inRange As Range, CI As Integer) As Integer
Dim myCell As Range
For Each myCell In Intersect(inRange.Parent.UsedRange, inRange)
If myCell.Interior.ColorIndex = CI Then
CountFormat = CountFormat + 1
End If
Next myCell
End Function

Used like

=CountFormat(A:A,6)

to count all the bright yellow cells in column A.

You can determine the proper colorindex by recording a macro where you apply
that color to a cell.

HTH,
Bernie
MS Excel MVP
 
Back
Top