On 9 July, 06:27, K wrote:
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

hi
question 1. see this site

http://cpearson.com/excel/colors.aspx
this is the functions i use and they work.

Quetion 2. i don't think UDFs work that way. the display syntax only
works with build in xl functions. or at least they never work for my
UDF's,

regards
FSt1