Try this variation of your function

Function SumColor(rColor As Range, rcolRange As Range, rSumRange As Range)
Dim rCell As Range
Dim iCol As Integer
Dim tmp As Variant
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rcolRange
If rCell.Interior.ColorIndex = iCol Then
tmp = rSumRange.Cells(rCell.Row - rcolRange.Row + 1, 1)
If IsNumeric(tmp) Then vResult = vResult + tmp
End If
Next rCell

SumColor = vResult
End Function


--

HTH

Bob

"K" wrote in message
...
Hi all, I have tried formula "=SumColor(A1,B2:B10,C2:C10)" in cell D1
after putting VBA function in module (see below) but I am getting
error "#VALUE". Basically I am tring to Sum cells values of a column
which are in the same rows of coloured cells of another column. I
have tow question. One how can i solve my fuction code and Two that
when you make your own function in excel VBA, with what code in your
function you can display Syntax while that function is applied. For
example when you put "IF" formula in the cell and as soon as you put
"IF(" you will see small box appearing with displaying Syntax like
"IF(logical_test,value_if_true,value_if_false) ". I want to do exactly
same in my function that when i put "=SumColor(" in the cell then
little box should appear displaying
"SumColor(color_cell,color_range,sum_range)". Please can any friend
can help me on this.

Function SumColor(rColor As Range, rcolRange As Range, rSumRange As
Range)

Dim rCell As Range
Dim iCol As Integer
Dim vResult

iCol = rColor.Interior.ColorIndex

For Each rCell In rcolRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(Columns(rCell.Row, rSumRange.Column))
+ vResult
End If
Next rCell

SumColor = vResult
End Function