# Thread: Sum By Color (Office 2002)

1. ## Sum By Color (Office 2002)

I swiped some code for a User-Defined-Function from the internet a few months back, placed it in an add-in and like the way it works except for one little problem... The code will give a correct result, but when the data is sorted and/or re-sorted, the formula results in a #Value error. [F9] will not fix the error but [Ctrl][Alt][F9] will! My spreadsheet is already set to Calculation|Automatic. Can something be added to the code that will force it to keep up with changes and sorts?

<pre>Function SumByColor(InputRange As Range, ColorRange As Range) As Double
' returns the sum of each cell in the range InputRange that has the same
' background color as the cell in ColorRange
' example: =SumByColor(\$A\$1:\$A\$20,B1)
' range A1:A20 is the range you want to sum
' range B1 is a cell with the background color you want to sum
Dim cl As Range, TempSum As Double, ColorIndex As Integer
' Application.Volatile ' this is optional
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempSum = 0
On Error Resume Next ' ignore cells without values
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then
TempSum = TempSum + cl.Value
End If
Next cl
On Error GoTo 0
Set cl = Nothing
SumByColor = TempSum
End Function</pre>

2. ## Re: Sum By Color (Office 2002)

I do not get that failure when I try it. Could you upload a workbook that shows the problem?

3. ## Re: Sum By Color (Office 2002)

Sure. I went ahead and added the code to a module within the worsheet that I've attached. I got the same #VALUE results. So, it's not working for me as an addin or as code in the sheet... Thanks for taking a look.

4. ## Re: Sum By Color (Office 2002)

First, one small comment about your sort macros. You can make them a little more efficient and eliminate some screen flashing by changing them to look something like this:

<pre>Sub Button1_Click()
'
' Button1_Click Macro
' Macro recorded 1/21/2005 by Ricky Ridgdill
'

'
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
</pre>

Now to your problem with the #Value error. It appears that when you do a sort, Excel disables access to the interior property of all cells on the worksheet, and does not enable access until after any recalculation that results from the sort has completed. That basically means that the cell color of all of the cells on the worksheet are not avaliable when the SumByColor function runs after the sort. This causes the #Value error. I am afraid I don't have a soultion to this problem. Maybe someone else will jump in with something.

5. ## Re: Sum By Color (Office 2002)

I removed the ( ' ) from the line Aplication.Volatile, making it active. Now [F9] works without having to press the Ctrl and Alt keys. But still not as automatic as I'd like.

And thanks for the tip on the "sort" macros. I should've known that the range did not have to be selected first. Excel itself, is not very efficient. I used the Recorder to dish up those sorts and Excel added the Range.Select lines.

6. ## Re: Sum By Color (Office 2002)

You can define a named range ColorTotals for the cells containing the SumByColor function. Recalculate this range in the macros that sort:

Sub Button1_Click()
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("ColorTotals").Calculate
End Sub

7. ## Re: Sum By Color (Office 2002)

Ahhhhh! I was looking for problems withing the SumByColor function, when the problem was really with the sorts. Once I realized that [F9] would work immediately after doing the sort, should've figured out that adding that calculate line to the end would be the answer.

Still, I wonder why Excel doesn't recalculate automatically after the sort? Under Options|Calculate, the method is set to Automatic. And, would simply adding the code to recalculate the sheet rather than the specific range work?

Thanks,

8. ## Re: Sum By Color (Office 2002)

Excel doesn't consider sorting a "change" so nothing needs to be recalculated. Your function is based on formatting changes, which doesn't trigger any event to change so calculations do not need to be updated

Steve

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•