A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Count cells in range w/specific background color?



 
 
Thread Tools Display Modes
  #1  
Old November 7th 08, 07:56 PM posted to microsoft.public.excel.worksheet.functions
Michael[_3_]
external usenet poster
 
Posts: 5
Default Count cells in range w/specific background color?

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


Ads
  #2  
Old November 7th 08, 08:33 PM posted to microsoft.public.excel.worksheet.functions
Spiky
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)
  #3  
Old November 8th 08, 12:28 AM posted to microsoft.public.excel.worksheet.functions
mikebres
external usenet poster
 
Posts: 89
Default Count cells in range w/specific background color?

Are you opening your Excel spreadsheet using automation? In otherwords do
you click a link on a web site or something similar to open the spreadsheet?

If you aren't opening your file this way, then ignore everything below.

I experienced this recently. If you are doing that, then the udf doesn't
save. To get it to work you would need to open one, save it to your hard
drive. Then open Excel normally and load the recently saved spreadsheet.
Create your udf, save it. Then the next time you open from a link the udf
wil be available.

Mike

"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
>
>
>

  #4  
Old November 8th 08, 05:51 AM posted to microsoft.public.excel.worksheet.functions
ShaneDevenshire
external usenet poster
 
Posts: 2,344
Default Count cells in range w/specific background color?

Hi,

I made a few changes to your code and everything seems to work.

Public Function CountCells(MyCells As Range) As Integer
Dim Cell As Range
Dim ctr As Integer
Application.Volatile
For Each Cell In MyCells
If Cell.Interior.ColorIndex = xlNone Or _
Cell.Interior.ColorIndex = 2 Or _
Cell.Interior.ColorIndex = 6 Then
ctr = ctr + 1
End If
Next Cell
CountCells = ctr
End Function

I don't know what the problem was because I just made the changes and tried
it.

If this helps, please click the Yes button
--
Thanks,
Shane Devenshire


"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
>
>
>

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Any formula to count number of cells with a specific color pattern Stu Excel Worksheet Functions 2 November 16th 07 05:13 PM
How to count specific letters in range of cells? Renee R.[_2_] Excel Discussion (Misc queries) 3 June 22nd 07 08:14 PM
count by specific text color in range of cell Tii99 Excel Discussion (Misc queries) 2 April 4th 06 09:58 AM
how to count cells with specific format (background color)? Blackheartedowl Excel Discussion (Misc queries) 1 February 8th 06 08:21 AM
How do you count cells with background color yellow? Stephanie D Excel Discussion (Misc queries) 6 October 18th 05 04:34 AM


All times are GMT +1. The time now is 07:14 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.