Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sums or counts cellsbased on specified fill color (2003)

    I came across this user defined function but don't know how to use it. I placed it in a module in my Personal.xls sheet. I think it counts the cells that have a color but don't know how to change the code to designate a specific color to count etc..............Thanks for your help.

    Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim lCol As Long
    Dim vResult

    ''''''''''''''''''''''''''''''''''''''
    'Sums or counts cells based on a specified fill color.
    '''''''''''''''''''''''''''''''''''''''

    lCol = rColor.Interior.ColorIndex

    If SUM = True Then
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
    vResult = WorksheetFunction.SUM(rCell, vResult)
    End If
    Next rCell
    Else
    For Each rCell In rRange
    If rCell.Interior.ColorIndex = lCol Then
    vResult = 1 + vResult
    End If
    Next rCell
    End If

    ColorFunction = vResult
    End Function

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums or counts cellsbased on specified fill color (2003)

    You set the color of a cell to the color you want to count or sum and pass that cell as the first argument to the function.
    Legare Coleman

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums or counts cellsbased on specified fill color (2003)

    OK call me stupid - When you say "You set the color of a cell to the color you want to count or sum and pass that cell as the first argument to the function" - how do you do this? With conditional formatting? How do you use the function? Do you put it at the bottom of a column - =Personal.xls!ColorFunction(A1:A10) - what is the syntax? Can you give an example of how to use this function?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sums or counts cellsbased on specified fill color (2003)

    To expand on Legare's reply: the formula

    =Personal.xls!ColorFunction(F1,A130)

    will count the number of cells in A130 with the same fill color as F1. So if you make cell F1 red, the formula will count red cells, and if you make F1 blue, the formula will count blue cells.

    If you want to sum the cell values instead of count the cells, use

    =Personal.xls!ColorFunction(F1,A130,TRUE)

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums or counts cellsbased on specified fill color (2003)

    Did Hans' more complete reply answer your question?
    Legare Coleman

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums or counts cellsbased on specified fill color (2003)

    Yes, thank you both.

  7. #7
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums or counts cellsbased on specified fill co

    How, if possible, would I change this function to sum cells where the content have a text colour? Or more to the point, not a text colour? We have a spreadsheet where amount unpaid are black and when paid they are changed to red text with strikethrough. I want to sum the black, i.e. unpaid, amounts.

    David

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sums or counts cellsbased on specified fill co

    How about this:
    <pre>Function ColorFunction(rRange As Range, Optional SUM As Boolean)
    Dim rCell As Range
    Dim vResult

    ''''''''''''''''''''''''''''''''''''''
    'Sums or counts cells based on the font not being red and struckout.
    '''''''''''''''''''''''''''''''''''''''



    If SUM = True Then
    For Each rCell In rRange
    With rCell.Font
    If Not (.Color = vbRed And .Strikethrough = True) Then
    vResult = WorksheetFunction.SUM(rCell, vResult)
    End If
    End With
    Next rCell
    Else
    For Each rCell In rRange
    With rCell.Font
    If Not (.Color = vbRed And .Strikethrough = True) Then
    vResult = 1 + vResult
    End If
    End With
    Next rCell
    End If

    ColorFunction = vResult
    End Function
    </pre>


    It will sum anything that is not Red and struck out.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums or counts cellsbased on specified fill co

    Thanks for the response.

    =Colorfunction(S39:Z39,-1) typed into a cell gives a #NAME? error. The error message in Excel2003 says "Invalid name error" and the function does not appear in the macros list. I've put this function in the worksheet's VBA module so it should show. Any ideas what I'm doing wrong?

    David

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sums or counts cellsbased on specified fill co

    The function should go in a normal module in your workbook not in the worksheet's module. Select the workbook in the project explorer in the VBEditor, choose Insert-Module, then paste the code into the new module (removing it from the worksheet module). Note: Functions do not appear in the macro dropdown.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums or counts cellsbased on specified fill co

    Rory

    This now works fine if I add or delete figures within the range. However, changing an amount from black to red/strikethrough does not force a recalculation, and neither does F9 (I'm set to recalc automatically). The only way seems to be to select the cell with the formula and go into it as if to edit it; exiting forces a recalc. This is impractical.

    Any thoughts?

    David

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Sums or counts cellsbased on specified fill co

    You could use the worksheet selectionchange event but that's a bit overkill I think. Personally, I would add a column to your spreadsheet labelled Paid and simply put a Y (or Yes) in it when paid. You can then have conditional formatting set up on your numbers column so that if the corresponding cell in the Paid column contains a Y, it automatically sets the Font to Red and struck through. You can then also simply use a SUMIF formula to total paid or unpaid amounts.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sums or counts cellsbased on specified fill co

    If you insert the line

    Application.Volatile

    at the beginning of the function, F9 will recalculate its result.

  14. #14
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sums or counts cellsbased on specified fill co

    Brilliant, thanks Rory and Hans

    David

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sums or counts cellsbased on specified fill color (2003)

    Han s,
    A new twist: Assume in column A of a worksheet, I have a list names, say 150, some of which I have formatted with different fill colors. In column B I have test scores for each respective name, but no fill color for any of these cells (I need to keep them "un-filled" for various reasons). At the bottom of column B, I would like to sum the scores for each name with a particular filled color in column A. For example, if there are 18 names in column A whose cell color is red, I would like to sum those respective scores at, say, cell B152. Any ideas?
    As always, thanks!

Page 1 of 2 12 LastLast

Posting Permissions

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