Set cell background color by color index

[Ms Bean]

Hi,

Now i m using color index to count the colored cell. The function that i m using is:

Please Login or Register  to view this content.

Then, in a cell put this formula using particular range:

=CountByColor(A1:A10,3)

This will count all the cells in A1:A10 that have the color red.

This function is post by shades, however the color index "3" is for red. Any of you know how what are the color index for blue, green and yellow?

Thanks

[jolivanes]

Ms Bean.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code in

Please Login or Register  to view this content.

tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window.


BTW, if you select a colored cell and run this macro, you'll get the Color Index Number.

Please Login or Register  to view this content.

[Ms Bean]

Jolivanes,

sorry for the inconvenience caused, next time i will do that according to the rules. Thanks for your info^^

However, when i save the file and reopen again, it show error to my formula. For example "=CountByColor(A1:A10,3)" it becomes #NAME? So what should i do to make it become normal with the formula that i set earlier?

Thanks

[JOHN H. DAVIS]

Take a look here.

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

[jolivanes]

Ms Bean

Have a look at the attached.
Slighly different UDF (borrowed from Mr Jim May)

[Ms Bean]

Thanks for your help