Function to Sum colored cells values not working

[K]

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

 

[FSt1]

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

 

[Bob Phillips]

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

 

[ManicMiner17]

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.

 

[ManicMiner17]

Or if your excel is bang up to date:

One of the new features in Excel 2010 is the ability to provide argument
descriptions for user-defined functions. These descriptions appear in
Function Arguments dialog box -- which is displayed after you choose a
function using the Insert Function dialog box.

http://spreadsheetpage.com/index.php/tip/user-defined_function_argument_descriptions_in_excel_2010