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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

(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.