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

 

[Fluff]

I didn't know you couldn't determine colors either.

You can if they are manually done, but CF is another matter.

 

[Claire Jackson]

The first two are. The range used to split into two colours based on a percentage but we don't need that now so rather than just messing with formulas we just turned them both to orange.

 

[Fluff]

What are task_start, task_end & task_progress?

 

[Claire Jackson]

Hi,

I've removed the first conditional format and the only that turns orange now is this one:
=AND(task_end>=K$10,task_start<L$10)

Task start is just a start date of a task and Task end is when the task is due to finish

 

[Fluff]

In that case you can just use

=COUNTIFS($K$10:$BN$10,">="&task_start,$K$10:$BN$10,"<="&task_end)

 

[Claire Jackson]

OMG thank you so much. Didn't realise it was so simple. thank you again ?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Claire Jackson]

Just one more question that totals the rows but what if I was to total the column?

 

[Fluff]

Try

=COUNTIFS(task_start,"<="&K10,task_end,">="&K10)

 

[Claire Jackson]

Hi sorry that doesn't work. I'll explain what I want to do.

Please see screenshot and what I need to do is total the amount of conditional formatted "orange" cells above the data. e.g. count all the bricklayer oranges and pop above for that particular day and same for joiners, plasterers etc.