a macro for conditional sum based on cell color index

[Khoshravan]

My question: How do I use conditional formatting in Excel to sum highlighted
cells?
Answer: I find the following macro in the internet:
source: http://answers.google.com/answers/threadview/id/67275.html
However it doesn't work and gives #value error. What is the problem with
this user-defined function?
If you have a better solution, let me know.

Function CFmt(RangeInQuotes, ColorIndex)

Dim Total As Double
Set Acell = Range(RangeInQuotes)
'Loop each cell in the range and if cell background eq to color index
sum it
For Each cell In Acell
If cell.Interior.ColorIndex = ColorIndex Then
Total = Total + cell.Value
End If
Next

CFmt = Total
End Function

 

[Chip Pearson]

I don't see where Conditional Formatting comes in, but you can sum
colored cells with the following function:

Function SumByColor(RR As Range, ColorIndex As Integer, _
Optional OfText As Boolean = False) As Variant
Dim R As Range
Dim Total As Double

If ColorIndex < 1 Or ColorIndex > 56 Then
SumByColor = CVErr(xlErrValue)
Exit Function
End If
On Error GoTo ErrH:
For Each R In RR.Cells
If OfText = True Then
If R.Font.ColorIndex = ColorIndex Then
Total = Total + R.Value
End If
Else
If R.Interior.ColorIndex = ColorIndex Then
Total = Total + R.Value
End If
End If
Next R
SumByColor = Total
Exit Function
ErrH:
SumByColor = CVErr(xlErrNum)

End Function

Here, you pass in the range of cells to test, the ColorIndex (1 <=
ColorIndex <= 56), and TRUE or FALSE indicating whether to text the
color of the font (TRUE) or of the background fill (FALSE or omitted).
With this function, you can sum the cells in A1:A10 that have red
backgrounds with

=SumByColor(A1:A10,3,FALSE)
to sum by font color, use
=SumByColor(A1:A10,3,TRUE)

The functions returns #VALUE if ColorIndex is invalid, or #NUM if a
non-numeric value is found.

For lots more about working with colors in Excel, see
http://www.cpearson.com/excel/colors.aspx

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Mike H]

Hi,

The color and colorindex of a cell are not the same as the colour of a cell
arrived at by conditional formatting and summing by the latter isn't easy.
The best method I know of is on the xldynamic site but tonight the site is
down and you may choose to look tomorrow

http://xldynamic.com/source/xld.ColourCounter.html

Chip pearson has some methods that i've never used but knowing the stuff he
produces I doubt you'll go far wrong there.

http://www.cpearson.com/excel/CFColors.htm

Mike

 

[Khoshravan]

Dear Chip Pearson

Thanks for your reply. I always enjoy your solutions. I am happy to see that
you are answering to my question.
Your function worked very well. thanks for that. In the mean time could you
please debug the sent Function, as I want to master my programming skills as
well.
PS) Yes what I want to do, has nothing to do with conditional formatting. If
I have mentioned it, I am wrong.

 

[Chip Pearson]

Your function worked very well. thanks for that. In the mean time could you
please debug the sent Function,

Your original function is more or less correct, with some minor
errors. A few things....

First, not all the variables are declared. While this is legal, it is
very bad programming practice, and a habit of which you should
disabuse yourself as soon as possible. See
http://www.cpearson.com/excel/DeclaringVariables.aspx for a discussion
of properly declaring variables, including the use of Option Explicit.

Next, the function requires that the range to test be enclosed in
quotes. That is,

=cfmt("A1:A15",3)
instead of
=cfmt(A1:A15,3)

If you omit the quotes, you'll get a #VALUE error. While one could
debate the merits of either approach, I would not use quotes and pass
an actual range, not a string representing a range (as in the second
syntax above).

Finally, the code does not check to make sure that the value being
added to Total is numeric. A non-numeric value would cause a #VALUE
error due to a type mismatch (error 13) error.

A corrected version of your function is shown below:

Function CFmt(RangeInQuotes, ColorIndex)
Dim ACell As Range
Dim Total As Double
Dim Cell As Range
Set ACell = Range(RangeInQuotes)

For Each Cell In ACell
If Cell.Interior.ColorIndex = ColorIndex Then
If IsNumeric(Cell.Value) = True Then
Total = Total + Cell.Value
End If
End If
Next
CFmt = Total
End Function

Call this from a cell with

=CFmt("A1:A15",3)

to sum elements in A1:A15 that have a red background. See ColorIndex
in VBA Help for a list of colors and their ColorIndex values.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Khoshravan]

Dear Chip

Thanks so much for putting your valuable time and energy to reply to my
question. Sorry for my poor programming skills and for asking such basic
questions. I should have checked with VBA help before asking.

 

[Khoshravan]

The site you mentioned, is not available. You told it will be down for a day
but now it is more than a day.