I have the need to count the number of cells that meet several criteria, one of which is the cell's fill color. I have written a VBA script that accomplishes this task singly. I can give it a reference color cell and a range of cells to check, and it returns an array of 1 or 0 if the target cell matches (1) or doesn't match (0) the color of the reference cell.
However, when I try to combine this array UDF with the SUM function and use it as an array function (CTRL+SHFT+ENTER), it doesn't seem to be itemizing the array cell by cell in my color function.
Here's my code:
Function IsColor(rColor As Range, rColorRange As Range) As Variant
Dim rCell As Range
Dim iCol As Integer
Dim vResult()
Dim vCounter As Integer
iCol = rColor.Interior.ColorIndex
For Each rCell In rColorRange
If rCell.Interior.ColorIndex = iCol Then
vCounter = vCounter + 1
ReDim Preserve vResult(1 To vCounter)
vResult(vCounter) = 1
Else: vCounter = vCounter + 1
ReDim Preserve vResult(1 To vCounter)
vResult(vCounter) = 0
End If
Next rCell
IsColor = vResult
End Function
And here's an example of usage:
{=SUM((D1:D5="abc")*(IsColor(B3,D1:D5)))}
Where column D is:
a (no fill)
ab (no fill)
abc (yellow fill, as is B3, the reference cell)
abc (no fill)
abcde (no fill)
The sum result should be just 1 cell (D3) which is BOTH yellow and "abc". However, the actual result I get is 2. It's as if the IsColor function was returning a positive result because at least one of the cells in the range was correctly color-filled instead of checking cell by cell within the array.
Any thoughts? All help is much appreciated!
Bookmarks