Count of Conditional Format Cells

Neil-D

New Member
Joined
Dec 9, 2009
Messages
17
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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Neil-D

New Member
Joined
Dec 9, 2009
Messages
17
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

Banned
Joined
Feb 8, 2003
Messages
5,378

ADVERTISEMENT

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

Neil-D

New Member
Joined
Dec 9, 2009
Messages
17
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!
 

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,156
Latest member
WDMix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top