interior colored cells counting
 

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.

interior colored cells counting
 

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

On Sun, 17 Oct 2010 08:44:03 -0700 (PDT), Konczér, Tamás
wrote:

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.

interior colored cells counting
 

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?

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

On Sun, 17 Oct 2010 08:44:03 -0700 (PDT), Konczér, Tamás
wrote:

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.

interior colored cells counting
 

2007 version

On Ribbon..............DataSort and FilterFilterFilter 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

On Sun, 17 Oct 2010 12:06:35 -0700 (PDT), Konczér, Tamás
wrote:

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?

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

On Sun, 17 Oct 2010 08:44:03 -0700 (PDT), Konczér, Tamás
wrote:

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.