adding colored cells only

[lindateammom]

I am wanting to add specifice colored cells only on my worksheet, the colors
represent stages that different orders are in and would like to keep a
running dollar amount total in each stage - maybe a conditional format? I
don't know how to tell excel to add only a certain color of cell - is it
possible without buying an additional program?

 

[Peo Sjoblom]

You need code for this

http://www.cpearson.com/excel/colors.aspx


info on how to install macros and UDFs

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

--


Regards,


Peo Sjoblom

 

[Mike H]

Hi,

If the colours are as a result of a conditional format then naybe it coud be
done with a formula but more detail qould be required. If the colours are
simply fill colours then you need a UDF and the one below does that

http://www.ozgrid.com/Excel/count-sum-cell-color.htm

Mike

 

[ShaneDevenshire]

Hi,

You must use VBA, here is some code you could modify:

Function SumFormats(R As Range, E As Range) As Integer
Dim cell As Range
Dim Total As Integer
Dim T As Boolean
Set S = E.Cells(1, 1)
Total = 0
For Each cell In R
T = True
With cell
If .Font.ColorIndex <> S.Font.ColorIndex Then T = False
If .Interior.ColorIndex <> S.Interior.ColorIndex Then T = False
If .Font.Bold <> S.Font.Bold Then T = False
If .Font.Italic <> S.Font.Italic Then T = False
If .Font.Underline <> S.Font.Underline Then T = False
End With
If T = True Then
Total = Total + cell
End If
Next cell
CountFormats = Total
End Function

If you are only interested in the background color take out the 4 lines for
Font.

This creates a custom function, which you use by typing a formula such as
this in the spreadsheet:

=SumFormats(A1:F13,H1)

This formula would check the format of H1 and sum all the cells in the range
A1:F13 which had the same color format.