Conditional formattting & CountColorCells

[Neil Pearce]

Good morning all,

With your help...

I have a workbook that ustilises COUNTCOLORCELLS() to generate the area and
perimeter of a plotted area.

I have another workbook that plots an area using CONDITIONAL FORMATTING
based on the outputs of length and width formulas.

I now wish to combime the two so that the area and perimeter of the
generated plotted shape are calculated. Herein lies the problem...

COUNTCOLORCELLS() will not count cells that are coloured through conditonal
formatting and therefore returns zero at all times.

Is there a way to count cells coloured that are generated from conditional
formatting? Alternatively can I use a method other than conditonal
formatting to plot varying areas- perhaps something along the lines of using
cell referencing... PLOT (A1) to (A+X,1+Y) where X & Y are generated lengths
and widths in two other cells?

You help, as ever, is much appreciated. Thank-you.


Cheers,

Neil

 

[Excel Ranger]

Came across something like this before over at mrexcel.com.
In Excel 2003 and earlier this could be accomplished with a lot of VBA.
In 2007, you can countif by color, so you should be able to whip up a
formula or two.

 

[Neil Pearce]

Unfortunately, my computer and laptop both run 2003. As does my work place
and university. So I am still at a loss and the problem still stands.

Out of interest does the 2007 COUNTIF BYCOLOR count conditionally formatted
cells?

 

[Bob Phillips]

It is not simple but it can be done. See
http://www.xldynamic.com/source/xld.CFConditions.html

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Neil Pearce]

Thanks Bob,

Is it a simple job of copying the Conditional Formatting Color Count
function as found in the grey box and pasting as a new function under Visual
Basic - Insert - Module, then use CFCOLORCOUNT(...) within the workbook?

Cheers,

Neil

 

[Bob Phillips]

Nothing is simple. CFColourCount is just a wrapper function as it states, to
give access to a counter, you also need the CFColorindex function.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Neil Pearce]

Hi Bob. I believe the reason why it wouldn't work is because there is an
additional closed bracket that isn't required in the code:

"CLng(CFColorindex(cell, text) ) = ciValue)"


Thank-you for your help. My building model is now working rather well indeed,

Cheers!

Neil