Count conditionally colored cells

[harieta]

Hi,
I've been trying to count cells by row which have been conditionally coloured
There are 3 rules for each cell
c3<=b19 is green
c3>b19 is red
when c3 is blank no formatting.
I used the UDF posted here but the count is incorrect. where do i go wrong?

Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
Dim Bambo As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long
Bambo = False
For CF1 = 1 To CellsRange.FormatConditions.Count
If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
Bambo = True
Exit For
End If
Next CF1
CF2 = 0
CF3 = 0
If Bambo = True Then
For Each CFCELL In CellsRange
dbw = CFCELL.FormatConditions(CF1).Formula1
dbw = Application.ConvertFormula(dbw, xlA1, xlR1C1)
dbw = Application.ConvertFormula(dbw, xlR1C1, xlA1, , ActiveCell.Resize(CellsRange.Rows.Count, CellsRange.Columns.Count).Cells(CF3 + 1))
If Evaluate(dbw) = True Then CF2 = CF2 + 1
CF3 = CF3 + 1
Next CFCELL
Else
COUNTConditionColorCells = "NO-COLOR"
Exit Function
End If
COUNTConditionColorCells = CF2
End Function

 

[Joe4]

Why not simply use same rules you used in your Conditional Formatting in a COUNTIF (or COUNTIFS) formula to count up the cells meeting those conditions?

 

[harieta]

Why not simply use same rules you used in your Conditional Formatting in a COUNTIF (or COUNTIFS) formula to count up the cells meeting those conditions?

Not sure if that will work, as each cell is formatted in relation to different date. For example cell c3 will have date 17th of Jan and be green, but cell d3 will have same date but coloured red (deadlines for each cell is different)

 

[Joe4]

Can you show us a larger sample size (more than one row) so we can get a better idea of how these rules work?
Be sure to include row numbers and column letters in your image so we can see exactly where all the data resides on your sheet.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[harieta]

Can you show us a larger sample size (more than one row) so we can get a better idea of how these rules work?
Be sure to include row numbers and column letters in your image so we can see exactly where all the data resides on your sheet.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

sample.xlsx
	A	B	C	D	E	F	G	H
1	1	2	3	4	5	6
2	24-Jan 11:00	17-Jan 11:17	13-Jan 13:23		17-Jan 11:48
3	22-Feb 17:23	16-Feb 14:21			20-Feb 17:47	20-Feb 21:27
4		15-Mar 14:16	20-Mar 12:11		20-Mar 13:59
5
6
7
8
9
10
11
12
13
14
15
16		deadline1	deadline 2	deadline 3	deadline 4	deadline 5	deadline 6
17
18		17-Jan	20-Jan	17-Jan	24-Jan	24-Jan	27-Jan
19		21-Feb	21-Feb	16-Feb	23-Feb	21-Feb	24-Feb
20		21-Mar	21-Mar	15-Mar	23-Mar	21-Mar	28-Mar
21		19-Apr	20-Apr	18-Apr	25-Apr	25-Apr	25-Apr
22		19-May	22-May	16-May	23-May	23-May	25-May
23		20-Jun	20-Jun	16-Jun	23-Jun	27-Jun	27-Jun
24		19-Jul	20-Jul	18-Jul	25-Jul	25-Jul	27-Jul
25		21-Aug	22-Aug	16-Aug	22-Aug	22-Aug	29-Aug
26		19-Sep	20-Sep	18-Sep	25-Sep	26-Sep	26-Sep
27		19-Oct	20-Oct	17-Oct	24-Oct	24-Oct	27-Oct
28		21-Nov	21-Nov	16-Nov	23-Nov	21-Nov	28-Nov
29		12-Dec	14-Dec	13-Dec	18-Dec	14-Dec	22-Dec
30
31
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F2:F13	Expression	=$F2=""	text	YES
F2:F13	Expression	=$F2>$G18	text	NO
F2:F13	Expression	=$F2<=$G18	text	NO
E2:E13	Expression	=$E2=""	text	YES
E2:E13	Expression	=$E2>$F18	text	NO
E2:E13	Expression	=$E2<=$F18	text	NO
D2:D13	Expression	=$D2=""	text	YES
D2:D13	Expression	=$D2>$E18	text	NO
D2:D13	Expression	=$D2<=$E$18	text	NO
C2:C13	Expression	=$C2=""	text	YES
C2:C13	Expression	=$C2>$D18	text	NO
C2:C13	Expression	=$C2<=$D18	text	NO
B2:B13	Expression	=$B2=""	text	YES
B2:B13	Expression	=$B2>$C18	text	NO
B2:B13	Expression	=$B$2<=C$18	text	NO
A2:A13	Expression	=$A2=""	text	YES
A2:A13	Expression	=$A2>$B$18	text	NO
A2:A13	Expression	=$A2<=$B$18	text	NO

I've attached mini sheet with all formatting

 

[Joe4]

I see. I didn't realize that there isn't a consistency to your Conditional Formatting formulas, and you actually have a bunch of formulas doing different things returning the same color.

There are lots of threads on the internet that will show you how you can count cells colored by Conditional Formatting.
Here is one, you can find many others with a simple Google search.

Count Conditionally Formatted cells

This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range B2:C11, column C has Conditi

www.get-digital-help.com

 

[Fluff]

If you want a macro, then have a look here Count cells in range that contain specific conditional formatted colors