Hi Jim from Va,

You're right, that's quite an editorial gaffe. That SUM call is so
unnecessary as well, so all-in, a bit slap-dash, I hope it's not one that I
recommend<g.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jim May" wrote in message
news:Rr%Ac.3061$HN5.1905@lakeread06...
Bob:

Thanks for your usual kind assistance; What I posted (the function) came
straight from my most recent book purchase - the title and author I will
leave unnamed (to protect unnecessary flaming,,,) another page which
bypassed the edit room (LOL)...

I changed my user name per your request (I'm Jim May - Virginia USA).

Again thanks,
Jim May


"Bob Phillips" wrote in message
...
JMay,

You define the first of the function arguments as a range, but pass a
number
to the function. You can either

pass the range with the colour
=SumByColor(A1,B4:B15)

maintain the call as is and change the code to handle an index

Function SumByColor(CellColor As Long, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal
iColor = CellColor
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

or allow either a range or an index

Function SumByColor(CellColor, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Long
Dim myTotal

If TypeName(CellColor) = "Range" Then
iColor = CellColor.Interior.ColorIndex
Else
iColor = CellColor
End If
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function

By the way, as you are iterating through each cell, you don't need to
sum
that range, that is

myTotal = WorksheetFunction.Sum(mytestcell) + myTotal

can be written more simply as

myTotal = mytestcell.Value + myTotal

Ande finally, as we see you so often, any chance of a more persoanl
handle
that we can address you by?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"JMay" wrote in message
news:Kt_Ac.3055$HN5.130@lakeread06...
In my worksheet when I enter =SumByColor(6,B4:B15) I'm getting
#VALUE!
Why??????????

Function SumByColor(CellColor As Range, SumRange As Range) As Long
Dim mytestcell As Range
Dim iColor As Integer
Dim myTotal
iColor = CellColor.Interior.ColorIndex
For Each mytestcell In SumRange
If mytestcell.Interior.ColorIndex = iColor Then
myTotal = WorksheetFunction.Sum(mytestcell) + myTotal
End If
Next mytestcell
SumByColor = myTotal
End Function