Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Calculate Based on Cell's Format (2003)

    I would like to sum all of the cells in a particular range that are NOT shaded. Any ideas?
    Thanks in advance.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Calculate Based on Cell's Format (2003)

    Try this from http://www.cpearson.com/excel/colors.htm


    <pre>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</pre>

    Jerry

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

    Re: Calculate Based on Cell's Format (2003)

    In the function mentioned by Jezza, you must specify -4142 as color index (this is the value of xlColorIndexNone, i.e. no fill color).

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

    Re: Calculate Based on Cell's Format (2003)

    Hans,
    How do I do that (specify), and how do I make sure this function is in every excel workbook I ever open.
    T%hanks,
    Jeff

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

    Re: Calculate Based on Cell's Format (2003)

    If you store the function in a module in your Personal.xls workbook, it'll be available in all workbooks.

    You'd use it in a cell formula as in the following example:
    <code>
    =Personal.xls!SumByColor(B2:B50,-4142)</code>

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

    Re: Calculate Based on Cell's Format (2003)

    See my Star <post:=118,382>post 118,382</post:> on Personal.xls.
    Legare Coleman

Posting Permissions

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