To add the SumByColor Function to your workbook.

Copy this text between the ................................

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

.................................................. ......

Now, with your workbook open hit Alt + F11 to open the Visual Basic Editor.

CTRL + r to open project explorer.

Right-click on your workbook/project and InsertModule.

Paste the text above into that module.

FileSave then hit Alt + q to return to Excel.

You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE) where 3 is the colorindex number(red in this
case)

If you don't care about font color just use =SumByColor(A1:A10,3)

To get a list of Excel's default colorindex numbers see David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/colors.htm


Gord Dibben MS Excel MVP



On Tue, 10 Apr 2007 07:56:04 -0700, GCC wrote:

I'm sorry I am quite a beginner when it comes to things like this. I have
looked at the code on the website you suggested but I don't understand how to
get this into excel? Any help you can give would be greatly appriciated.

Thanks.

"Dave F" wrote:

If you're using XL 2007 this is possible without customization. If you're
using an earlier version of Excel you can do this with macros. See:
http://www.cpearson.com/excel/colors.htm for more info.

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"GCC" wrote:

I was wondering if it was possible to write a formula in excel that says for
example 'add the numbers in all cells that are coloured purple?'