+ Reply to Thread
Results 1 to 3 of 3

sum up cells by background colour

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    98

    sum up cells by background colour

    Hi all,

    I would like to sum up certain cells depending on the background colour they have. On the attachment you will find an example where values need to be summed up according to the background colour. I found the following macro online which enables me to do that. However every time I want to add a values in the range of F9 to F30 and change the background colour the sums do not update automatically.
    Attachment 168438
    Function SumColor(Color As Range, Range As Range) As Long

    Dim Cell As Range
    Dim ColorIndexNumber As Integer
    Dim ColorSum

    ColorIndexNumber = Color.Interior.ColorIndex

    For Each Cell In Range
    If Cell.Interior.ColorIndex = ColorIndexNumber Then
    ColorSum = WorksheetFunction.Sum(Cell.Value) + ColorSum
    End If
    Next Cell

    SumColor = ColorSum

    End Function

    I would appreciate your help as I am stuck and cant continue.

    I am looking forward to hearing from you.
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: sum up cells by background colour

    Custom functions don't automatically update when you change the format (color) of a cell. They will automatically update if you change the value of a cell.

    An imperfect solution: Add this at the top of the macro, it will recalculate the custom function when any value (not background color) on the sheet is changed. You could also press the F9 key to force the custom functions to update.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    07-06-2010
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    98

    Re: sum up cells by background colour

    Excellent Thank you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1