Home 
Search 
Today's Posts 
#1




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 reopening 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 builtin Excel functions instead? Right now I'm having to go thru and manuall count up the cells and hardcode 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 
#2




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 reopening 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 builtin Excel functions instead? Right now I'm having to go thru and manuall count up the cells and hardcode 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 builtin 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




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 reopening 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 builtin Excel functions instead? Right now I'm having to go thru and manuall count up the cells and hardcode 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




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 reopening 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 builtin Excel functions instead? Right now I'm having to go thru and manuall count up the cells and hardcode 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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Any formula to count number of cells with a specific color pattern  Excel Worksheet Functions  
How to count specific letters in range of cells?  Excel Discussion (Misc queries)  
count by specific text color in range of cell  Excel Discussion (Misc queries)  
how to count cells with specific format (background color)?  Excel Discussion (Misc queries)  
How do you count cells with background color yellow?  Excel Discussion (Misc queries) 