Count number of coloured cells

Hi all,

I have attached my workbook.

I am looking for at solution so count the blue cells per week. See attached image.

Right now I have used conditional formatting to color the cells, through this formula:

=AND(M$11>=$J13;M$11<=$K13)

Then in the green line, where I want to count the amount of colored cells in the week, i have used this code:

Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue = CountColor.Interior.ColorIndex
Set rCell = CountRange
For Each rCell In CountRange
  If rCell.Interior.ColorIndex = CountColorValue Then
    TotalCount = TotalCount + 1
  End If
Next rCell
GetColorCount = TotalCount
End Function

Then added this formula to the cell:
=GetColorCount(M13:M200;$X$5)

But due to the cells being colored by conditional formatting this do not work.

Do you have any suggestions?

Thanks!2020-02-18_11-40-31.jpg

As the question (and the file) is about UDF, it probably suits better to https://www.excelforum.com/excel-pro...ng-vba-macros/ subforum.

But anyway:
Accessing the formatting applied to a cell by conditional formatting is not an easy tesk. so probably it's better to directly count the situations, which are markled by conditional formatting. The simple UDF could be:

Function CountCurrentWeek(rStartAndEndColumns As Range, lCurrentWeek As Long) As Long
Dim arr As Variant, i As Long, lTmpResult As Long
arr = rStartAndEndColumns.Value
For i = 1 To UBound(arr)
  If arr(i, 1) <= lCurrentWeek And arr(i, 2) >= lCurrentWeek Then lTmpResult = lTmpResult + 1
Next i
CountCurrentWeek = lTmpResult
End Function

The same could be obtained (and then posting in formulas subforum is just right) with regular formula:

Formula:

=COUNTIFS($J$13:$J$157,"<="&M11,$K$13:$K$157,">="&M11)

(this approach is presented in a copy of original sheet)

From XL2010 onwards there's a method called DisplayFormat which is able to count colours from CF.

Have a look into that.

Edit: DisplayFormat will only work when called from another sub, not from worksheet. So stick with Kaper's solution.