Need Help. Want to add cells that have a background color.

  • Thread starter Thread starter Marc.
  • Start date Start date
M

Marc.

Hi. Posted something similar yesterday, and was confused
as hell by the response. Have since visinted
cpearson.com, and found an example, but as I try to
implement it, I keep getting a #name? error, no matter
what I try.

Here is what I want.

I want to add the values of cells within a range that do
not have a background color.

Here is what was suggested:
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

**AND I PASTED the following text in the cell where I
wanted the result.

=SUMBYCOLOR(J5:J30),0,FALSE) where I want cells that do
not have a color (assuming it is 0, also tried with every
number from 1-20) and False, because it is the background
I want, not the font.

Any help would be appreciated. If you have an easier
method, I would appreciate that too.
 
Thanks very much Dan, I think this sets me on the right
track. The only problem, is I don't want to count the
actual cells that are colored, but rather add the value
that is in these cells. As I highlight a cell, I want
that number to be omited from the total.

Background info.. I use excel to keep track of my monthly
budget. I have 2 totals, "Total" and "Left to pay". As
transactions go through, I highlight them, and would
like "Left to pay" to show only the remaining amount,
i.e. total value of the cells that have no background.
 
I must be doing something wrong. I just copied your
code, and ran the formula you provided and still get the
#name? error. Is there something I am supposed to
declare maybe that I haven't?

Also, in case you are wondering, I have been typing these
codes in the "general" module of my workbook.
 
No.. Someone else had asked me to make sure when I type
my vb code, that I put it in the general module (one of
the 2 dropdown tabs in a vb window) and not workbook
module. Thought it would help, sorry if it confused :)
 
Marc,

In the project explorer in the VB window (if you don't see the project
exlorer hit ctrl + R) right click in your project and choose "Insert" ->
"Module". Double click that module (Module1) to open the code window and
paste the following code into the window

Public Function CellColourCount(ColouredCells As Range) As Integer
'ColouredCells is the range containing the Cells to be counted
CountColour = Selection.Interior.ColorIndex
For Each Cell In ColouredCells
TestColor = Cell.Interior.ColorIndex
If TestColor = CountColour Then i = i + 1
Next
CellColourCount = i
End Function

Public Function CellColourSum(ColouredCells As Range) As Double
'ColouredCells is the range containing the Cells to be counted
CountColour = Selection.Interior.ColorIndex
For Each Cell In ColouredCells
TestColor = Cell.Interior.ColorIndex
If TestColor = CountColour Then Total = Total + Cell.Value
Next
CellColourSum = Total
End Function

Dan E
 
Back
Top