Color Counting Function

[trough]

Tried to convert a working subroutine into a function but now it doesn't work. I'm not good with Range variables so I'm sure it's user error. Intent is to have function count the cells in a given range that have the same color as a single specified cell.

Excel 2010
	A	B	C	D
1		6	<-- This should be 3
2		6	<-- This should be 1
3
4
5
Sheet1

Cell Formulas
Range	Formula
B1	=CountColor(A1,B4:D5)
B2	=CountColor(A2,B4:D5)

Function CountColor(rColor As Range, rRange As Range) As Long
    
'rColor is cell with background color of interest
'rRange is range of cells to consider

Dim rMyRng As Range
Dim iBackColor As Long

On Error Resume Next
    
iBackColor = 0
For Each rMyRng In rRange
 If rMyRng.DisplayFormat.Interior.Color = rColor.DisplayFormat.Interior.Color Then
  iBackColor = iBackColor + 1
 End If
Next

CountColor = iBackColor

End Function

 

[Joe4]

So I'm back to figuring out why the DisplayFormatCount subroutine works while my function does not.

I have searched high and low, and have not really found anything good on it, other than it is very difficult to do it this way.
If you do Google searches, you will see that many people have had this problem, and I have not really come across any good solutions other than using the Conditions instead of the colors.
If you do figure it out, please post back, as I would be very interested in seeing how it is done (as would many others, I am sure).

 

[MARK858]

Microsoft do state that Display.Formats doesn't work in UDF's...

msdn.microsoft.com/en-us/vba/excel-vba/articles/range-displayformat-property-excel

 

[Rick Rothstein]

So I'm back to figuring out why the DisplayFormatCount subroutine works while my function does not.

There are certain functions that cannot be used in a UDF (user defined function), some because they would involve making changes to the Excel sheet which formulas and, by extension, UDFs are not allowed to do, others because (I believe) they would involve "live tracking" complex object structures. SpecialCells is one of those functions that will not work in a UDF because the things it calculates can be changed by events outside of its control. Conditional Formatting appears to be another one of those and, since one of the main functionalities in DisplayFormat is to examine values resulting from a complex plethora of changeable conditions, it too will not work inside a UDF. You will have to rely on using the underlying logic behind the Conditional Formatting to test whether the cell has been colored with the color you are processing or not.