I have been trying to count blank colored cells in 2003. I have tried
several VBA and other threads listed here. Especially Chip's cppearson
site. none of the formulas direct or macroed are working. It seems that
anything related to color is not a recognized name. I am not fluent on
macros but have used them when presented in simple measures. Thanks in
advance for any help!!

Hi,

You aren't very desriptive on exactly what you are doing so here's something
you may be able to build on. Right click a sheet tab, view code and paste
this in. It will check the range A1 - A100 and count all the yellow cells.
Note if the colour is yelllow because of conditional formatting then that
requires a different approach.

Sub standard()
Set myrange = Range("A1:A100")
For Each c In myrange
If c.Interior.ColorIndex = 6 Then
yellowcells = yellowcells + 1
End If
Next
MsgBox yellowcells
End Sub

Mike

"Whois Clinton" wrote:

I have been trying to count blank colored cells in 2003. I have tried
several VBA and other threads listed here. Especially Chip's cppearson
site. none of the formulas direct or macroed are working. It seems that
anything related to color is not a recognized name. I am not fluent on
macros but have used them when presented in simple measures. Thanks in
advance for any help!!

"none of the formulas direct or macroed are working" could mean you have not
applied them correctly.

They work for a lot of other people.

One question.

How have the cells been given their color fill?

If manually, count them using the Countbycolor code from Chip's site.

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

If by Conditional Formatting, see Chip's site at

http://www.cpearson.com/excel/CFColors.htm


Gord Dibben MS Excel MVP


On Wed, 14 May 2008 11:31:00 -0700, Whois Clinton
wrote:

I have been trying to count blank colored cells in 2003. I have tried
several VBA and other threads listed here. Especially Chip's cppearson
site. none of the formulas direct or macroed are working. It seems that
anything related to color is not a recognized name. I am not fluent on
macros but have used them when presented in simple measures. Thanks in
advance for any help!!

Thanks so much Mike. For some reason I was successful with that process.
However, I have one more complication. In my selected range some columns of
cells are merged and some are not. It is counting the 2 merged cells as 2
but I need them counted as one block. Is there any hope for me?? I was so
excited to see something work.

"Mike H" wrote:

Hi,

You aren't very desriptive on exactly what you are doing so here's something
you may be able to build on. Right click a sheet tab, view code and paste
this in. It will check the range A1 - A100 and count all the yellow cells.
Note if the colour is yelllow because of conditional formatting then that
requires a different approach.

Sub standard()
Set myrange = Range("A1:A100")
For Each c In myrange
If c.Interior.ColorIndex = 6 Then
yellowcells = yellowcells + 1
End If
Next
MsgBox yellowcells
End Sub

Mike

"Whois Clinton" wrote:

I have been trying to count blank colored cells in 2003. I have tried
several VBA and other threads listed here. Especially Chip's cppearson
site. none of the formulas direct or macroed are working. It seems that
anything related to color is not a recognized name. I am not fluent on
macros but have used them when presented in simple measures. Thanks in
advance for any help!!