UDF Count the number of cells coloured by conditional formatting

[Claire Jackson]

I've been searching for days on this and finally have had to ask for help. I can see its been asked many times before but each example I find doesn't work.

I have a spreadsheet with part of it conditionally formatted with orange based on certain criteria. All I want to do is count the amount of coloured cells and I just can't get it to work.

The cell colour I am looking for is in cell BP1 and the formula in BP2 is this: =@CountConditionColorCells($K$13:$BN$22,BP1)

the code I have is this:

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

 

[GWteB]

Perhaps changing the first line into the second one resolves your issue.

If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then

If CellsRange.FormatConditions(CF1).DisplayFormat.Interior.ColorIndex = ColorRng.DisplayFormat.Interior.ColorIndex Then

 

[Claire Jackson]

No, that just gives me an error "#VALUE!"

 

[GWteB]

Not sure what you're exactly trying to achieve, but how about ...

If CellsRange.DisplayFormat.Interior.ColorIndex = ColorRng.DisplayFormat.Interior.ColorIndex Then

 

[Claire Jackson]

Still the same error.
Basically all I'm trying to do is get some sort of formula/code that will tell me how many of the cells that have been coloured in orange by the conditional format in the range.

 

[Fluff]

@GWteB unfortunately DisplayFormat does not work in a UDF.

 

[Fluff]

What is the CF rule that you are using to highlight the cells?

 

[Claire Jackson]

There are 3:
=AND(task_start<=K$10,ROUNDDOWN((task_end-task_start+1)*task_progress,0)+task_start-1>=K$10)
=AND(task_end>=K$10,task_start<L$10)
=AND(TODAY()>=K$10,TODAY()<L$10)

 

[GWteB]

@GWteB unfortunately DisplayFormat does not work in a UDF.

I know UDF's have limitations in changing colors etc.. I didn't know you couldn't determine colors either.

 

[Fluff]

There are 3:

Which one is for orange?