Count cells which have particular format?

  • Thread starter Thread starter RD
  • Start date Start date
R

RD

I have a large sheet where one column has some cells format to a color fill
to indicate a certain condition requiring attention. My question is: What
formula can I use to count or sum up the quantity of cells with this
formatting?

RD
 
Hi
you'll need VBA for this. Below a repost (watch the
linebreaks)
------
using a procedure from Bob Phillips and Harlan Grove you
may try the
following:

=SUMPRODUCT(--(ColorIndex(A1:A100)=3))
to count all red cells (background color) within the range
A1:A100

or
=SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=3))
to count all red cells (font color) within the range
A1:A100

To get the colorindex of a specific cell use
=ColorIndex(A1)

Adapt this to your requirements

------
'Code to paste in one of your modules

'----------------------------------------------------------
-----------
Function ColorIndex(rng As Range, _
Optional text As Boolean = False) As
Variant
'----------------------------------------------------------
-----------
' Function: Returns the colorindex of the supplied
range
' Synopsis:
' Author: Bob Phillips/Harlan Grove
'
'----------------------------------------------------------
-----------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

If rng.Areas.Count > 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If

iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent)

If rng.Cells.Count = 1 Then
If text Then
aryColours = DecodeColorIndex(rng, True,
iBlack)
Else
aryColours = DecodeColorIndex(rng, False,
iWhite)
End If

Else
aryColours = rng.Value
i = 0

For Each row In rng.Rows
i = i + 1
j = 0

For Each cell In row.Cells
j = j + 1

If text Then
aryColours(i, j) = _
DecodeColorIndex(cell,True,iBlack)
Else
aryColours(i, j) = _
DecodeColorIndex(cell,False,iWhite)
End If

Next cell

Next row

End If

ColorIndex = aryColours

End Function

Private Function WhiteColorindex(oWB As Workbook)
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function DecodeColorIndex(rng As Range, text As
Boolean, idx As
_
Long)
Dim iColor As Long
If text Then
iColor = rng.font.ColorIndex
Else
iColor = rng.Interior.ColorIndex
End If
If iColor < 0 Then
iColor = idx
End If
DecodeColorIndex = iColor
End Function
 
Excel does not have a built-in function to count/sum cells
with a particular format. You'll need a UDF (user-defined
function). Here's an old one from MVP George Simms:

http://tinyurl.com/2crhv

HTH
Jason
Atlanta, GA
 
...
...
' Author: Bob Phillips/Harlan Grove ...
iWhite = WhiteColorindex(rng.Worksheet.Parent)
iBlack = BlackColorindex(rng.Worksheet.Parent) ...
Private Function WhiteColorindex(oWB As Workbook)
Dim iPalette As Long
WhiteColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &HFFFFFF Then
WhiteColorindex = iPalette
Exit Function
End If
Next iPalette
End Function

Private Function BlackColorindex(oWB As Workbook)
Dim iPalette As Long
BlackColorindex = 0
For iPalette = 1 To 56
If oWB.Colors(iPalette) = &H0 Then
BlackColorindex = iPalette
Exit Function
End If
Next iPalette
End Function
Click to expand...
...

Kindly don't associate *MY* name with code so monumentally POINTLESS and *YOUR*
WhileColorindex and BlackColorindex functions. Maybe you had some purpose in
mind when you wrote tham, but as you never use the values assigned to iWhite and
iBlack in your main udf, they're a waste of cycles and storage.

What I actually wrote is in

http://www.google.com/[email protected]

though my preferred approach is to avoid screwing around with multiple special
purpose functions, and instead use a single function to access most range,
worksheet and workbook properties, as in

http://www.google.com/groups?selm=0vb89.20087$Ke2.1629609@bgtnsc04-news.ops.worldnet.att.net

Note: I've since fixed the sign problem I mentioned in that post.
 
RD

Can you just countif or sumif using the same criteria as that for the CF?

i.e =COUNTIF(A1:A32,"=16")

Gord Dibben Excel MVP
 
Well, we may be getting close. Pardon my ignorance but what did you mean by
CF? Colorfill? The =16 as the criteria doesn't work for the background
color. The formula works great if "16" is the contents of the cell. For
example if I have 3 - 16's in the range the formula returns 3. Perfect, now
if I could just get it to count the cells only if the background color is
16, or 2 or whatever...

As for everyone else's comments I tried the module approach and they work if
the summing the values in the formatted cells. As above, just tell me how
many cells have color...

thanks all for the assistance..
 
OK Got it: I noticed that one of the fellows above used google to search. I
did the same but changed sum to count. Long story a little shorter, I ended
up at http://www.cpearson.com/excel/colors.htm where he has some excellent
formulas and programming. I had some trouble with the syntax on the Function
but eventually got to what is below:

Function CountByColor(InRange As Range, WhatColorIndex As Integer, Optional
OfText As Boolean = False) As Long
'
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True

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

End Function

Thanks Mr. Pearson and ll who contributed. Much appreciated.
RD
 
RD

I was thinking the cells were colored via Format>Conditional Formatting.

Hence my suggestion to count using the same condition(s).

If manually colored you will go with the CountByColor UDF from Chip or the UDF
Bob posted.

Gord
 
Harlan Grove wrote:
[...]
Kindly don't associate *MY* name with code so monumentally POINTLESS
and *YOUR* WhileColorindex and BlackColorindex functions.
Click to expand...

will do
 
Back
Top