^ Cells colored by conditional formatting will not work;the cells must have an interior color. o

SumColor(CellColor, SumRange) The arguments are

CellColor—The address of a cell with the target color. SumRange—The range of cells to be searched.

Function SumByColor(CellColor As Range, SumRange As Range) Dim myCell As Range Dim iCol As Integer Dim myTotal iCol = CellColor.Interior.Colorlndex 'get the target color

For Each myCell In SumRange 'look at each cell in the designated range

'if the cell color matches the target color

If myCell.Interior.Colorlndex = iCol Then

'add the value in the cell to the total myTotal = WorksheetFunction.Sum(myCell) + myTotal

End If Next myCell SumByColor = myTotal End Function

Figure 4.5 shows an example worksheet using the function.

Sum cells based on interior color.

0 0

Post a comment