Count cells in range that contain specific conditional formatted colors

[Gregory123987]

What I need to do is to count the number of conditional highlighted cells in each row (range) for each color. Ultimately, if the color exists in the range, I intend to have a value of 1, else a value of 0. Here is a worksheet with an example of what the result should be for the first 5 rows. It if matters, my version is Excel 2019 Home & Student. I believe the target system for this spreadsheet is Excel 2016.

FYI - I have spent several hours searching the internet and found several possible solutions but they don't seem to work on this spreadsheet, although they do work on their own spreadsheets. I have removed the code from the workbook and the formulas. I am also thinking there is something in THIS workbook that is preventing the formulas from working. However, I am unable to tell what color is in the cells.

FYI - The conditional formatting uses the standard colors red, yellow, orange and blue in the indicated row in this image. Thank you in advance for your help.

S2F Data Laps 20210831.xlsm
																											AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
1
2
3
4																											# Participants per Day Totals
5																											4	6	6	6	7	6	5	6	2	6	6	6	7	7	6	7	7	6	7	7	6	6	4	5	4	5	5
6																											LAPS RUN
7																											TH	F	S	M	T	W	TH	F	S	M	T	W	TH	F	S	M	T	W	TH	F	S	M	T	W	TH	F	S			What should appear
9
10																											1	2	3	5	6	7	8	9	10	12	13	14	15	16	17	19	20	21	22	23	24	26	27	28	29	30	31
11
12																																														0										1	0	0	0
13																											6	0	6	0	4								6	0		0	4		6											1	1	1	0
14																															4	1		0		0	5	1	4	0	5	0	3	1	2	0	4	0								1	1	1	1
15																											4	0	5	0	4	1	4	0		0	6	1	5	0	4	0	5	1	4	0	4	0	4	1	5	0	4			1	1	1	1
16																											5	0	5	0	4	1	5	0	5	0	5	1	5	0	4	0	5	1	5	0	6	0	5	1	5	0	5			etc
17																												0	7	0	4	1	7	0	5	0	7	1	7	0	4	0	7	1	4	0	7	0	7	1	7	0	4
18																												0	3	0	4	1	3	0		0	5	1	5	0	5	0	4	1	4	0	6	0		1	4	0	4
19																											3	0	4	0	4	1	4	0		0	4	1	4	0	4	0	3	1	3	0	4	0	4	1		0	4
20
Jul

Cell Formulas
Range	Formula
AA5:BA5	AA5	=COUNT(AA12:AA19)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
BG10	Cell	contains a blank value	text	NO
BF10	Cell	contains a blank value	text	NO
BE10	Cell	contains a blank value	text	NO
BD10	Cell	contains a blank value	text	NO
AA12:BA19	Expression	=IF(AA12="","",IF(COUNTBLANK($AA12:AA12),IF(COUNT(OFFSET(AA12,,,,MATCH(2,1/($AA12:AA12=""))-COLUMNS($AA12:AA12)))>$J12+COUNT(OFFSET($AA12,,,,MATCH(TRUE,$AA12:AA12="",0))),COUNT(OFFSET(AA12,,,,MATCH(2,1/($AA12:AA12=""))-COLUMNS($AA12:AA12))),""),MOD(IF($J12<10,$J12)+COUNT($AA12:AA12)-1,IF(COLUMNS($AA12:AA12)<10,10,1E+100))+1))=10	text	NO
AA12:BA19	Expression	=IF(AA12="","",IF(COUNTBLANK($AA12:AA12),IF(COUNT(OFFSET(AA12,,,,MATCH(2,1/($AA12:AA12=""))-COLUMNS($AA12:AA12)))>$J12+COUNT(OFFSET($AA12,,,,MATCH(TRUE,$AA12:AA12="",0))),COUNT(OFFSET(AA12,,,,MATCH(2,1/($AA12:AA12=""))-COLUMNS($AA12:AA12))),""),MOD(IF($J12<10,$J12)+COUNT($AA12:AA12)-1,IF(COLUMNS($AA12:AA12)<10,10,1E+100))+1))=5	text	NO
AA12:BA19	Expression	=IF(AA12="","",IF(COUNTBLANK($AA12:AA12),IF(COUNT(OFFSET(AA12,,,,MATCH(2,1/($AA12:AA12=""))-COLUMNS($AA12:AA12)))>$J12+COUNT(OFFSET($AA12,,,,MATCH(TRUE,$AA12:AA12="",0))),COUNT(OFFSET(AA12,,,,MATCH(2,1/($AA12:AA12=""))-COLUMNS($AA12:AA12))),""),MOD(IF($J12<10,$J12)+COUNT($AA12:AA12)-1,IF(COLUMNS($AA12:AA12)<10,10,1E+100))+1))=3	text	NO
AA12:BA19	Expression	=AND(AA12<>"",($J12+COUNT(AA12:$AA12))=1)	text	NO

 

[Eric W]

I think DisplayFormat was available in Excel 2016. If so, you can use this technique to write a UDF that counts the Conditional Formatting colors. You'd need to find the color codes of the colors you're looking for.

Using DisplayFormat in a UDF

Hi, I came accross this topic Excel VBA User Defined Function that counts cells with conditional formatting for counting cells with CF and there was an answer offering a neat workaround which uses the handy Evaluate function to make the DisplayFormat work inside a UDF. Here is an adaption...

www.mrexcel.com

 

[Eric W]

I was curious enough to write it up:

Function DFColor(ByVal R As Range, ColorVal As Long) As Double
Dim r1 As Range

    Application.Volatile
    DFColor = 0
    For Each r1 In R
        If Evaluate("Helper(" & r1.Address() & ")") = ColorVal Then DFColor = DFColor + 1
    Next r1
    
End Function


Private Function Helper(ByVal R As Range) As Double
    Helper = R.DisplayFormat.Interior.Color
End Function

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
5	1	2	3	4	5		3
Sheet18

Cell Formulas
Range	Formula
G5	G5	=dfcolor(A5:E5,255)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:E5	Expression	=MOD(A5,2)	text	NO

The color code for the basic "red" color is 255.

 

[Gregory123987]

I was curious enough to write it up:

Function DFColor(ByVal R As Range, ColorVal As Long) As Double
Dim r1 As Range

    Application.Volatile
    DFColor = 0
    For Each r1 In R
        If Evaluate("Helper(" & r1.Address() & ")") = ColorVal Then DFColor = DFColor + 1
    Next r1
    
End Function


Private Function Helper(ByVal R As Range) As Double
    Helper = R.DisplayFormat.Interior.Color
End Function

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
5	1	2	3	4	5		3
Sheet18

Cell Formulas
Range	Formula
G5	G5	=dfcolor(A5:E5,255)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A5:E5	Expression	=MOD(A5,2)	text	NO

The color code for the basic "red" color is 255.

I tried this and it worked very well. What are the color codes for the basic yellow, orange and blue as indicated in my OP?

 

[Rick Rothstein]

You can find out the values by putting them in cells and having VBA tell you. For example, put the orange color in cell A1, then go into the VBA editor and enter this in the Immediate Window (press CTRL+G if you don't see it)...

? Range("A1").Interior.Color

Whatever value it prints is the color value to use for it.

 

[Gregory123987]

I am trying to get the count of cells conditionally formatted. Range.Interior.Color each have the value of 16777215

Below is the VBA commands and responses
? Range("BD10").Interior.Color
16777215
? Range("BE10").Interior.Color
16777215
? Range("BF10").Interior.Color
16777215
? Range("BG10").Interior.Color
16777215

 

[Gregory123987]

I tried this and it worked very well. What are the color codes for the basic yellow, orange and blue as indicated in my OP?

You can find out the values by putting them in cells and having VBA tell you. For example, put the orange color in cell A1, then go into the VBA editor and enter this in the Immediate Window (press CTRL+G if you don't see it)...

? Range("A1").Interior.Color

Whatever value it prints is the color value to use for it.

Thank you both, I was able to figure out the other color codes.

 

[Eric W]

Glad we could help! Thanks for the update.