Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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.

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sumifcolor (excel sr1)

    Thanks!!

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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
  •