Count of Conditional Format Cells

[Neil-D]

I have a large spreadsheet which I require people to regularly complete. Due to the complexity of my business there are 180 columns, each of which has a variety of rules towards its completion.

I have used conditional formatting to check as many of the rules as possible (e.g. if somebody enters that delivery is required then the columns for delivery address cannot be blank).
Each column has the same conditional formatting but can have 1,2 or 3 rules. Min is always 1.
Most columns are fairly unique conditional rules.
There is a variable number of rows each time it is filled out (i.e. people add rows as required) but they copy and paste an existing row so conditional formatting is copied as well.
All conditional formatting rules use the "formula" setting and nearly all use many "AND", "OR" statements to make all the necessary checks.
If a cell is found to have failed the validation checks (e.g. is blank when should have an entry, has an entry when it should be blank) then it highlights the cell in red.

I now want to have a count of the number of "red" cells to show me quickly if it has been completed "correctly" or not.

I have written the vba code to check all the cells but cannot find out how to record if the conditional formatting is being used or not. "colorindex" returns the original colour of the cell not the conditional formatting.

Any help greatly appreciated.

 

[Andrew Poulsom]

Maybe this will help you:

http://www.cpearson.com/Excel/CFColors.htm

 

[xld]

You have two ways,

either, test the cells in the same manner as the conditional formatting does

or, use this techique http://xldynamic.com/source/xld.CFConditions.html which effectively counts those conditions that are coloured the given colour

 

[Richard Schollar]

Nice article Bob.

 

[xld]

Nice article Bob.

Thank you Richard, I do try

 

[Neil-D]

Thanks for the links but both of these functions only appear to work if the conditional formatting is done using the "cell value" option. For "formula" option it does not work (and my worksheet only uses formulas as I use AND/Or to check several cells in the same row).

I have put the code into my workbook and when I use the functions I get "#Value!" error.
If I put a simple conditional format in a cell in the same worksheet (e.g. cell value = 1) then the functions return the correct value.

I am doing something wrong?

 

[xld]

No, mine works with expressions as well. It is a complete solution unlike Chip's.

 

[Neil-D]

xld, I am still struggling with this.

I left it and started again from scratch.
I copied the "CFColorIndex" and "CFColorCounting" into a module in my file. Do I need to copty any more for CFColorCounting to work?

When I put the function CFColorCounting into my spreadsheet I get the error message - "Compile Error. Wrong number of arguements or invalid property assignment" and the header for CFColorCount is highlighted in yellow -

Public Function CFColorCount(rng As Range, _
ciValue, _
Optional text As Boolean = False)

As far as I can tell, it is the same as your example so what have I done wrong?

Looking further down the code I have also noticed that two lines, that look like they should be the same, are slightly different -

CFColorCount = -CLng(CFColorindex(rng, text) = ciValue)

CFColorCount = CFColorCount - _
CLng(CFColorindex(cell, text)) = ciValue)

There are double ")" brackets on the second one after text. Is this correct?

Thanks for any help. I am really struggling here!

 

[Neil-D]

* bump *

 

[xld]

You have to pass it the range to test, and the colour index to test for, such as

=CFColorCounting(A1:A10,3)

to test for red.