Wednesday, April 14, 2010

Excel Function with VBA - Sum cells with background color

Ever wanted to sum cells you have highlighted with a background color? If so, follow these steps:

Open Excel 2007 spreadsheet
Press alt +F11
Click Insert from menu, Select Module
Insert the below VBA code and then close the VBA view
Select cell in your spreadsheet
Enter the following formula -  =SumColor(A1:A33,6)
Your formula will be different based on the range of cells you are working with - Ex: A1:A33 and the background color you would like to add.
Background colors are:
Black = 1, White = 2, Red = 3, Green = 4, Blue = 5, Yellow = 6

Option Explicit

Function SumColor(Area As Range, Ci As Integer)

Dim sng As Single, rng As Range

For Each rng In Area

If rng.Interior.ColorIndex = Ci Then sng = sng + rng.Value

Next rng

SumColor = sng

End Function

No comments:

Post a Comment