Counting Formats

[CraigAllen]

Hello, I have a work sheet which has a list of things running from A8 down to
A400. They have been manually classified by use of the cell fill colour. Is
there a way that I can show the number of green rows or red row etc?

 

[xlm]

You need a macro to do this.

try this UDF :
Copy and paste the below code to your module in VBE
in cell B8, place this formula =CountColor($A$8,$A$8:$A$400)
assuming that A8 is green, this will count all the green format cells
repeat this for red by changing the lookup value in the formula

Pls note that this will not work if you any of your color format in these
cells used conditional formatting.

Function CountColor(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell) + vResult
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
CountColor = vResult
End Function

Does this do what you want?
HTH
--
If this posting was helpful, please click on the Yes button

Thank You

cheers,

 

[Chip Pearson]

You need to use VBA code. You can't do it with Excel's built in
functions. See http://www.cpearson.com/excel/colors.aspx for details,
examples, and downloadable code.

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)