View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Spiky Spiky is offline
external usenet poster
 
Posts: 622
Default Count cells in range w/specific background color?

On Nov 7, 1:56*pm, "Michael" wrote:
Hi! This seems oh so simple and it works when I 1st use this function in a
cell formula, but upon closing and re-opening the spreadsheet (Excel 2003)
it gives me a Name error everwhere I used this function!

The function's purpose is to count up and return the number of cells in a
Named Range that don't have a grey background color. I need this information
in a percentile function to calculate the percentage of completion for
different sections on a spreadsheet. It works right, but only when I 1st
enter the function in my percentage of completion cells.... later after
restarting the spreadsheet, it no longer works. What's up with that?

Below is the function:

Public Function CountCells(MyCells As Range) As Integer

Dim MyCell As Range
Dim ctr As Integer

* * For Each MyCell In MyCells
* * * * ' Only count the Clear background, White background, or Yellow
background cells
* * * * If MyCell.Interior.ColorIndex = -4142 Or MyCell.Interior.ColorIndex
= 2 Or MyCell.Interior.ColorIndex = 19 Then
* * * * * * ctr% = ctr% + 1
* * * * End If
* * Next
* * Set MyCell = Nothing
* * CountCells = ctr%

End Function

And it's used in cell's with the following formula:
=C89/CountCells(GreenSection1)

GreenSection1 being a named range of cells and C89 being a cell with a
number in it. There's ALOT of formulas of this nature in this spreadsheet
though, this is only an example of how I'm using the UDF.

Can anybody explain why this only works when I input the formula (with UDF)
into a cell, and then no longer works once the spreadsheet has been
restarted? Is there any other way to accomplish what this UDF is trying to
do with built-in Excel functions instead?

Right now I'm having to go thru and manuall count up the cells and hard-code
the number into my formulas, and MAN oh MAN is this tedious!!! Plus it's not
very maintainable, if we add or remove rows from the spreadsheet the numbers
will need to be manually updated again. Ughhhh! 8-(

Thanks!

- Michael


I can't explain the problem. But I can verify that there isn't a way
to do this with the built-in functions. I would guess the first place
to start looking for a solution is the location where you entered the
UDF code. IOW, is it the proper location.

The morefunc group has a function that can access background colors,
and it provides an easy way to install into a particular file so UDFs
used in that file will still work on any computer. I use it
occasionally for summing cells with various background color. I don't
see the NAME! error with morefunc. Search at download.com. (there are
probably other UDF sets to be found on the web, too)