On 09/07/2010 11: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 K,

This isn't straightforward (at least in the versions of Excel I have
used - upto & including 2003)

Look at
http://www.eng-tips.com/viewthread.cfm?qid=79365&page=9
for some helpful discussion & links

Rgds.