Function COUNTConditionColorCells(CellsRange As Range, ColorRng As Range)
'make the worksheet always update
Application.Volatile
'define my variables

Dim Work As Boolean
Dim dbw As String
Dim CFCELL As Range
Dim CF1 As Single
Dim CF2 As Double
Dim CF3 As Long

Work = False
'for the first conditional format to the number of conditions in the range
For CF1 = 1 To CellsRange.FormatConditions.Count
    'if the first condition colour is in the range then start counting
    If CellsRange.FormatConditions(CF1).Interior.ColorIndex = ColorRng.Interior.ColorIndex Then
    Work = True
Exit For
    End If
Next CF1
CF2 = 0
CF3 = 0
If Work = True Then
For Each CFCELL In CellsRange
    'count the colours in the range
    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