Counting cells based on cell formatting

[amk1023]

Hello,

I have a large set of numerical data (cells I8:O24607) in which each
cell has been formatted to have either no fill or one of 5 different
colored (light yellow, yellow, orange, red, or black) fills. The cell
values and formatting were copied and pasted into this worksheet from
other worksheets so there is no conditional formatting applied to the
current sheet.

The formatting represents a quality rating and I would like to count
the number of cells that have either no fill or the light yellow fill
in each column. Is this possible to do using the IF function and
having the cell fill be the condition and the TRUE value be "1" then
summing the TRUE values? If so, how do I enter the cell fill as the
condition? Are there other ways to do this?

Thank you,

Andrea

 

[Gary''s Student]

See:

http://www.cpearson.com/excel/colors.aspx

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.ColourCounter.html

 

[ShaneDevenshire]

Hi,

Excel's spreadsheet functions can't count cells based on formatting, yet.
But you can do it with VBA.

The following is fancier than you need but it might give you ideas:

Function CountFormats(R As Range, E As Range) As Integer
Dim cell As Range
Dim Total As Integer
Dim T As Boolean
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
T = True
With cell
If .Font.ColorIndex <> S.Font.ColorIndex Then T = False
If .Interior.ColorIndex <> S.Interior.ColorIndex Then T = False
If .Font.Bold <> S.Font.Bold Then T = False
If .Font.Italic <> S.Font.Italic Then T = False
If .Font.Underline <> S.Font.Underline Then T = False
End With
If T = True Then
Total = Total + 1
End If
Next cell
CountFormats = Total
End Function

To use this function you enter

=CountFormats(B2:F13,A1)

What it does is count how many cells in the range B2:F13 have a format that
matches A1. It is checking the formatting of bold, italic, underline, font
color and fill color.

 

[Picman]

I’ve tried this and it works the first time but does not update if you change
the format of a cell in the range. The result will change if you copy and
paste the formula into another cell. I thought that maybe recalculating the
sheet might work but it didn’t. any suggestions?

 

[T. Valko]

Add this line of code before the line: Dim cell As Range

Application.Volatile

Then the function will recalculate whenever the file calculates. However, it
still will not calculate when a format changes. A change in format does not
trigger a calculation. It's for that reason I discourage using functions
based on formats. You can also hit function key F9 at any time to
recalculate.

 

[Picman]

It didn't solve the problem. this is how the code looks now, did i do
something wrong?

Function CountFormats(R As Range, E As Range) As Integer
Application.Volatile
Dim cell As Range
Dim Total As Integer
Dim T As Boolean
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
T = True
With cell
If .Font.ColorIndex <> S.Font.ColorIndex Then T = False
If .Interior.ColorIndex <> S.Interior.ColorIndex Then T = False
If .Font.Bold <> S.Font.Bold Then T = False
If .Font.Italic <> S.Font.Italic Then T = False
If .Font.Underline <> S.Font.Underline Then T = False
End With
If T = True Then
Total = Total + 1
End If
Next cell
CountFormats = Total
End Function

 

[T. Valko]

There's nothing you can do about it. As I noted in my other reply, this is
why I discourage the use of formulas based on formats. Changing a cells
format does not trigger a calculation. So, when you have formulas based on a
cells format the formula doesn't update (calculate) when you change the
cells format. The formula will only update when some other event triggers a
calculation or you manually force a calculation.

 

[Picman]

Thanks for the help anyway.

There's nothing you can do about it. As I noted in my other reply, this is
why I discourage the use of formulas based on formats. Changing a cells
format does not trigger a calculation. So, when you have formulas based on a
cells format the formula doesn't update (calculate) when you change the
cells format. The formula will only update when some other event triggers a
calculation or you manually force a calculation.

 

[T. Valko]

You're welcome.

--
Biff
Microsoft Excel MVP

Thanks for the help anyway.

 

[ebloch]

look at ASAPUtilities for functions using cell formats

Eric