1. ## sumifcolor (excel sr1)

Hi,
I am trying to create a sumif function that will respond to the different colors in a range. Ie sum up the all of the blue cells within the given range.
I have created a function to count the occurence of a given color in a range and was wondering if I could modify it some how. Here is the code for the colorcount below:

Function Colorcount(MyRange As Range, color As Variant) As Long

Dim oCell As Range
Dim lngCount As Long
For Each oCell In MyRange
If oCell.Interior.ColorIndex = color Then
lngCount = lngCount + 1
End If
Next oCell
Colorcount = lngCount
Set oCell = Nothing
End Function

2. ## Re: sumifcolor (excel sr1)

Try this function:

Function ColorSum(MyRange As Range, MyColor As Variant) As Double
Dim oCell As Range
Dim dblSum As Double
For Each oCell In MyRange
If oCell.Interior.ColorIndex = MyColor And IsNumeric(oCell) Then
dblSum = dblSum + oCell
End If
Next oCell
ColorSum = dblSum
Set oCell = Nothing
End Function

Chip Pearson has a whole series of functions dealing with cell colors on his website: Functions For Working With Cell Colors.

Thanks!!

4. ## Re: sumifcolor (excel sr1)

Function SumColors(rngRange As Range, lngColor As Long) As Double
Application.Volatile True
Dim rngCell As Range
SumColors = 0
For Each rngCell In rngRange
If rngCell.Font.ColorIndex = lngColor Then _
SumColors = SumColors + rngCell.Value
Next rngCell
End Function

5. ## Re: sumifcolor (excel sr1)

One thing you need to be aware of with these functions. They are not LIVE even with application volatile. Changing the color of a cell is NOT considered a change, so will NOT trigger a calc, so will NOT update. You will have to MANUALLY press F9 to get it to recalc if the ONLY change is to the colors and not any cell contents.

A little bit of an extension of your question, (but it arises with the colored cells counting thing alot):
These test for EXPLICIT color settings! Conditional formatting is a different beast altogether.
See Chip Pearson for some code to look into this.

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
•