I got cells with various colors/numbers and I would like to add up those which are red, etc.
|Check out our Excel VBA Resources|
I have a custom function that I have written for this. To use push Alt+F11 and go to Insert>Module and paste in this code:
Function SumColor(rColor As Range, rSumRange As Range)
'Written by Ozgrid Business Applications
Dim rCell As Range
Dim iCol As Integer
iCol = rColor.Interior.ColorIndex
For Each rCell In rSumRange
If rCell.Interior.ColorIndex = iCol Then
vResult = WorksheetFunction.Sum(rCell) + vResult
SumColor = vResult
Then push Alt+Q to return to Excel and Save.
Now in any cell put:
Where A10 interior Color is Red (or any other color) and A1:A20 is the range you want to Sum.
OzGrid Business Applications
If Cells(HC, xcol).Interior.ColorIndex = 8 Then