|
|||||||||
|
|||||||||
|
||||||||||||||||||||||||||||
» Sum by color using VBA in Microsoft Excel
 | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
It's not possible to add the values in a range depending on each cells background color with the built-in SUM()-function. With the custom function below you can add the contents of cells depending on their background color:
Function SumByColor(InputRange As Range, ColorRange As Range) As Double
' returns the sum of each cell in the range InputRange that has the same
' background color as the cell in ColorRange
' example: =SumByColor($A$1:$A$20,B1)
' range A1:A20 is the range you want to sum
' range B1 is a cell with the background color you want to sum
Dim cl As Range, TempSum As Double, ColorIndex As Integer
' Application.Volatile ' this is optional
ColorIndex = ColorRange.Cells(1, 1).Interior.ColorIndex
TempSum = 0
On Error Resume Next ' ignore cells without values
For Each cl In InputRange.Cells
If cl.Interior.ColorIndex = ColorIndex Then
TempSum = TempSum + cl.Value
End If
Next cl
On Error GoTo 0
Set cl = Nothing
SumByColor = TempSum
End Function
The function is used in the same way as the built-in worksheetfunctions. InputRange contains the cells with the values that the function is going to add, ColorRange is a cell reference to a cell containing the background color you want restrict the adding to. In Excel 5/95 the variables InputRange and ColorRange must be declared as Variant instead of Range. The function can easily be adapted for use with other worksheetfunctions that you want to use on cells with different background colors. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
|
READER COMMENTS (view all comments) |
||||||||||||||||
|
No comments have been submitted. | ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
 | ||||||||||||||||
The Intelligent Investor: The Classic Bestseller on Value Investing
Microsoft Windows XP Inside Out
Essentials of Investments with Standard & Poor's Educational Version of Market Insight + PowerWeb + Stock Trak Coupon
Accounting the Easy Way
PowerPoint® 2002 For Dummies®
Windows XP for Dummies | ||||||||||||||||
| ||||||||||||||||
RELATED MICROSOFT EXCEL TIPS
 |
• Excel VBA books
• Accounting books
• Business Plans
• MS Office books
• Taxes books
|
Terms
and Conditions of use |
|
|
Copyright © 2003 ExcelTip.com |
|