udf macro which can count Conditional formatting?

[excelNewbie22]

hi,
is there's udf macro that can count cells in conditional formatting, no matter the color?
i tried google for it, even find some in here, but none with what i need

 

[Zot]

Not sure if this is what you meant

Sub CountCF()

Dim cell As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")
 
Set cell = ws.Cells.SpecialCells(xlCellTypeAllFormatConditions)
Debug.Print cell.Count

End Sub

 

[excelNewbie22]

thanks zot,
i meant this

test.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	2	3	4	5	6	7	8		how many colored cells?
2									5
3			a1:e1 bigger then 2				equal to 8
4
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G1	Cell Value	=8	text	NO
F1	Cell Value	=8	text	NO
A1:E2,A4:E25,A3:B3,E3	Cell Value	>2	text	NO

 

[Zot]

Oh.. it is not Conditional Formatting but count colored cell

Book1
	A	B	C	D	E	F	G	H	I
1	2	3	4	5	6	7	8		how many colored cells?
2									5
3			a1:e1 bigger then 2				equal to 8
Sheet1

Cell Formulas
Range	Formula
I2	I2	=nColored(A1:G1)

The UDF

Function nColored(rng As Range)
Dim cell As Range
For Each cell In rng
    If Not cell.Interior.ColorIndex = -4142 Then nColored = nColored + 1
Next
End Function

 

[dmt32]

Hi,
Range.DisplayFormat property may do what you want - however, it does not work inside a UDF
There are are couple of workarounds that have been published

Excel VBA User Defined Function that counts cells with conditional formatting

I am trying to write a UDF that counts the number of cells that have conditional formatting. I wrote the following sub that works like a charm: Sub SumCountByConditionalFormat() Dim cellrngi As Ra...

stackoverflow.com

or

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

you may find helpful

Dave

 

[Zot]

Thanks @dmt32. I misunderstood him.

My 1st solution wasn't thinking much. I was actually counting all Conditional Formatting on a sheet.

So, from the references you provided, my UDF is

Book1
	A	B	C	D	E	F	G	H	I
1	2	3	4	5	6	7	8		how many colored cells?
2									5
3			a1:e1 bigger then 2				equal to 8
Sheet1

Cell Formulas
Range	Formula
I2	I2	=nCFColor(A1:G1)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G1	Expression	=$G$1=8	text	NO
A1:E1	Expression	=A$1>2	text	NO

Function nCFColor(rng As Range) As Long
Dim x As Double
Dim cell As Range
Application.Volatile
For Each cell In rng
    x = Evaluate("CFColor(" & cell.Address() & ")")
    If x <> 16777215 Then nCFColor = nCFColor + 1
Next cell
End Function

Function CFColor(c As Range) As Double
CFColor = c.DisplayFormat.Interior.Color
End Function

 

[dmt32]

Thanks @dmt32. I misunderstood him.

@Zot No worries - done same myself many times. Hopefully, suggestion will resolve for OP.

Dave

 

[excelNewbie22]

kudos !
thank you zot!