interior colored cells counting

[Konczér, Tamás]

Hi,
There is a column with dates or empty cells. Some of the cells having
dates has "yellow" background color. I would like to count only the
"yellow" cells. I realized that in this case I am not able to use
DCOUNT because I don't know how to filer to "yellow" bg color. Thank
you for any help.

 

[Gord Dibben]

How do the cells receive the color?

Manually or by conditional formattting?

Which version of Excel?

2007 can filter by color if manually colored.

Earlier versions can not.

If by CF, what is the criterion for "yellow" cells.?


Gord Dibben MS Excel MVP

 

[Konczér, Tamás]

Hi Gord,
thank you for your comments. These are manually formatted cells, under
Office 2003 (company laptop).

However I use Off2007 on my home comp., how should I filter for
color?

 

[Gord Dibben]

2007 version

On Ribbon..............Data>Sort and Filter>Filter>Filter by Color

2003 would require VBA function to return cell color index number on which you
filter.

Function CellColorIndex(InRange As Range, Optional _
OfText As Boolean = False) As Integer
'
' This function returns the ColorIndex value of a the Interior
' (background) of a cell, or, if OfText is true, of the Font in the cell.
'
Application.Volatile True
If OfText = True Then
CellColorIndex = InRange.Font.ColorIndex
Else
CellColorIndex = InRange.Interior.ColorIndex
End If
End Function

In an adjacent column enter =CellColorIndex(cellref)

Drag/copy down then filter on the number you want.

See Chip Pearson's site for more on that.

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

Note this bit........................

You can download a module file that contains all the code on this page. The
various procedures within the modColorFunctions.bas module call upon one
another, so you should import the entire module into your project, rather than
copying single procedures.


Gord