I need to count all the cells in a column containing a date between $C$2 and $D$2 which are highlighted with color 33. I would like to be able to do this with a single-cell formula. I have not been having any luck.
The custom function is this:
Function GetCellColor(rRange As Range) As Long
GetCellColor = rRange.Cells(1).Interior.ColorIndex ' Return the cell color.
Using it in a cell Formula like this works:
Then I can drag it down all the rows and I get a 1 for all the H cells that are highlighted color 33 containing a date between $C$2 and $D$2; all others show 0. Then I sum the column in another cell.
But what I REALLY want is a single-cell formula using SUMPRODUCT to give me this sum, something like this:
This always produces 0.
How can I write a function like GetCellColor to work in a situation like this? It seems like there is something fundamentally different about functions that work in this fashion, and I just haven't got a clue.
I haven't figured out what Excel is doing behind the scenes in a case like this. I always assumed that it was iterating over the range, calling the function each time with the current cell passed in as a single-cell range.
Can anyone shed light on this for me?