Count cells by color and get "#NAME?"

[Ucpaul]

I appreciate any explanation to this:

I use: =ColorFuction($A$1,B1:B10,FALSE) to count number of cells in B1:B10 that have the same color as cell A1. When I typed in this formula in Excel 07, I alway got #NAME?

I typed in the same formula the other day and it worked fine. Anything wrong with my formula, or do I need to press other key rather than "Enter" key?

Thanks

 

[norulen]

the reason why you #NAME is because its a UDF (User Defined Formula)
This formula would work only in the sheet in which this code for this formula has been saved

 

[Ucpaul]

Could you explain or show me how to go around this or to make it work. I'm new to VBA.

Thanks for help.

 

[norulen]

DO you have the code with you?
WOuld you want this to work only in one file or would you want this to be working in all the files?

 

[Ucpaul]

I don't know how to get that code? (again, I'm a VBA beginner).

Could you show mw both ways?

Thanks.

 

[norulen]

You might find this link useful
http://www.mrexcel.com/forum/excel-questions/450188-color-function-excel-2007-a.html
http://www.ozgrid.com/forum/showthread.php?t=27182

 

[Ucpaul]

I solved it. Thanks for your help. I'm at a higher level now

 

[norulen]

Glad you could solve it Suggest you to post the solution that worked for you. So that people may get benefitted from it in future

 

[Ucpaul]

I used the link that you suggested, then copy the code (for color count) onto a customer define module in my spreadsheet.

 

[norulen]

Thanks for putting it back here. Happy that this link helped you