# Thread: Calculate Based on Cell's Format (2003)

1. ## 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?

2. ## 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>

3. ## 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. ## 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. ## 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. ## Re: Calculate Based on Cell's Format (2003)

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

#### Posting Permissions

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