Function to Sum colored cells values not working

K

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
 
F

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
 
B

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
 
M

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top