UDF to count colored cells always gives zero

I suspect it will be very difficult for us to debug this for you without a sample spreadsheet to test the code on. Since debugging is a very important skill (certainly important enough to develop some debugging skills of your own), I would suggest the following:

1) If you are unfamiliar with the debugging tools available in VBA and how to use them, I would suggest reviewing a page like this: http://www.cpearson.com/excel/DebuggingVBA.aspx

2) I would probably start debugging this by adding a break point at the end with statement in sub icountcolors() (or a stop statement after the end with). Run the procedure up to this point. When VBA enters debug mode, I will be looking for:
a) what value did it return for LR?
b) perhaps more interestingly, what range is stored in Rng? This might be easiest by setting a watch for Rng.address. By whatever means you devise, the idea is to make sure that Rng refers to the range that you expect it to. If it does, then move on. If not, then you need to figure out what went wrong during the block with.

3) Set a stop of breakpoint in countcolors before it does anything.
4) check to make sure that RangeToCount contains the range you expect it to contain.
5) Step through the function. (or stop after the If...Then statment inside of the loop).
a) What cell is "colorcell" referring to on this pass through the loop?
b) Check the value returned by colorcell.interior.colorindex. Is it an RGB value that you would expect, knowing the color that is in each cell?
6) Go back to 5 and continue stepping through the loop (you will probably want to perform this on a small test range), checking the values for each colorcell.

For countcolors to return 0, it seems that it must never actually return True for the If test inside of this loop. The overall idea for the second part of this is to see if you can see why it is never seeing this test as true.

Please Login or Register  to view this content.

ColorIndex is represented by a single numeric value. RGB() should use .Color instead of .ColorIndex

In addition, if you are using conditional formatting it will not detect the color.

Please Login or Register  to view this content.