Sum Highlighted Cells
 

I have highlighted cells within a spreadsheet by changing the box color to
yellow. Is there anyway to easily create a formula to SUM these cells for
me? Thanks

Sum Highlighted Cells
 

This is code from Chip Pearson's site which sums cells with a given backgound
colour
or font.

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

You can call this function from a worksheet cell with a formula like
=SUMBYCOLOR(A1:A10,3,FALSE) <==Background colour 3

=SUMBYCOLOR(A1:A10,3,TRUE) <== Font colour 3

HTH

"Jenny" wrote:

I have highlighted cells within a spreadsheet by changing the box color to
yellow. Is there anyway to easily create a formula to SUM these cells for
me? Thanks

Sum Highlighted Cells
 

Here are 2 custom formulas that will work for you. The first one uses a
named range called "MyData" the other formula does exactly the same thing as
the first, only you provide the cell address when you enter the formula into
the spreadsheet.

CalcYellowDogCellRedux(A1:X100)

interior color index of 6 is yellow..
----------------------------------------------------------------------
Function CalcYellowDogCells() As Double

Dim wb As Workbook
Dim ws As Worksheet
Dim r As Range
Dim dblSum As Double
Dim l As Long

Application.Volatile
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

ws.Activate
Set r = Range("MyData")

For l = 1 To r.Cells.Count
If r.Cells(l).Interior.ColorIndex = 6 Then
dblSum = dblSum + r.Cells(l).Value
End If
Next l

Set wb = Nothing
Set ws = Nothing
Set r = Nothing
CalcYellowDogCells = dblSum
Exit Function

End Function
----------------------------------------------------------------------
Function CalcYellowDogCellsRedux(CellAddresses As Range) As Double

Dim wb As Workbook
Dim ws As Worksheet
Dim r As Range
Dim dblSum As Double
Dim l As Long

Application.Volatile
Set wb = ActiveWorkbook
Set ws = wb.Worksheets("Sheet1")

ws.Activate
Set r = CellAddresses

For l = 1 To r.Cells.Count
If r.Cells(l).Interior.ColorIndex = 6 Then
dblSum = dblSum + r.Cells(l).Value
End If
Next l

Set wb = Nothing
Set ws = Nothing
Set r = Nothing
CalcYellowDogCellsRedux = dblSum
Exit Function

End Function
----------------------------------------------------------------------
--
Kevin Backmann


"Jenny" wrote:

I have highlighted cells within a spreadsheet by changing the box color to
yellow. Is there anyway to easily create a formula to SUM these cells for
me? Thanks

Sum Highlighted Cells
 

See http://www.xldynamic.com/source/xld.ColourCounter.html for a working
solution

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"Jenny" wrote in message
...
I have highlighted cells within a spreadsheet by changing the box color to
yellow. Is there anyway to easily create a formula to SUM these cells for
me? Thanks