Trouble using custom function in excel

[Subash.Raj]

Hi,

I am trying to find the sum of number of cells with a particular color in a column. Decided to go for a custom function with the following code.

Function colorfunc(rColor As Range, rRange As Range, Optional SUM As Boolean)

Dim rCell As Range

Dim lCol As Long

Dim vResult

''''''''''''''''''''''''''''''''''''''
'Written by Ozgrid Business Applications
'www.ozgrid.com
'Sums or counts cells based on a specified fill color.

'''''''''''''''''''''''''''''''''''''''
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
colorfunc = vResult

End Function

But I am facing #Name? error when trying to use the function, eventhough the function gets listed when I start typing '=' in the cell.

The function is placed in a module as shown in the screenshot below.

I am trying to use it in the same worksheet, but not able to use it. I am able to use some simple functions but not this one.

I am getting #Ref or #Name error. I am using Excel 2010

 

[cgcamal]

Hi Subash,

Try with this example:

Excel Workbook
	E	F	G	H
2	3			Sum values from cells with same fill color as E2 (Yellow)
3				8
4		1		Counts cells with fill color as F5 (Red)
5				4
6
7	4
...

Excel 2010

Cell Formulas
Range	Formula
H3	=colorfunc(E2,E2:F7,TRUE)
H5	=colorfunc(F5,E2:F7,)

-You can sum when add the optional argument "TRUE" at the end
-You can count when you use last argument as FALSE or when is ommited

Hope this helps,

Regards