Counting colored cells

[khank]

I was wondering if I could count "colored" cells.

From cpearson.com the VB coding is:

=SUMPRODUCT(--(COLORINDEXOFRANGE(E28:E68,FALSE,1)=38))

I want to use #38 (Pink) as the color used in different cells.

Is there a formula to use instead of going to VB?

[DonkeyOte]

If the colour code is set manually then no is the answer
(given 2008 obviously you have no VBA support per se - you can use XLM4 I believe - or AppleScript - neither of which I know much about)

If there is logic determining the format (ie conditional formatting) then yes, ape the logic of the conditional formatting in your function.

[khank]

Is there a "normal" coding to count "Colored" cells?

[DonkeyOte]

I don't know what "normal" coding relates to.

To reiterate, if the colour is being determined by logic [still unknown] then you should simply be looking to ape that same logic via a standard formula to conduct the count.

If you're formatting manually/arbitrarily then you have a few issues ...

1. Formatting a cell is not volatile action per se.
As a result of the above: assuming you can identify the colour (read on) you still can not be 100% sure that your calculations will be accurate all of the time
Using a Volatile construct will reduce the risk of error but will not remove it

2. If your version is XL2008 then obviously you have no VBA support so the more traditional routes (UDF) are not open to you.

3. If you can use older XLM calls then you might consider using those via Defined Names to retrieve the colour of a given cell (GET.CELL information 63).
However, you would I think (realistically) need to store the results per cell and calculate off those stored values.

The alternative would be to investigate creating full blown XLM scripts and/or use of AppleScript - neither of which I can help you with personally I am afraid.

If you are not using XL2008 - please state which version(s) you are using.