Microsoft Office Tutorials and References
In Depth Information
Applying SUM / COUNT by Color
'Limit the range to the used range¶
'Intersect method takes cells common to both Range and¶
'Range.Worksheet.UsedRange¶
Set Range = Intersect(Range, Range.Worksheet.UsedRange)¶
'Only use the first cell of criteriaColor¶
Set criteriaColor = criteriaColor(1)¶
For Each Rng In Range.Cells¶
If Rng.Interior.Color = criteriaColor.Interior.Color Then¶
CountIfColor = CountIfColor + 1¶
End If¶
Next Rng¶
End Function¶
' * * * * *¶
Function SumIfColor(ByVal Range As Range, _¶
ByVal criteriaColor As Range, _¶
Optional ByVal sum_range As Range) As Variant¶
'Variable declaration¶
Dim i As Long¶
'Make it volatile¶
Application.Volatile True¶
'Validations, only one area¶
If Range.Areas.Count > 1 Then¶
SumIfColor = CVErr(xlErrValue)¶
End If¶
'Limit the range to the used range¶
'Intersect method takes cells common to both Range and¶
'Range.Worksheet.UsedRange¶
Set Range = Intersect(Range, Range.Worksheet.UsedRange)¶
'Check for a valid range to sum¶
If sum_range Is Nothing Then Set sum_range = Range¶
'Only use the first cell of criteriaColor¶
Set criteriaColor = criteriaColor(1)¶
'Loop through each cell in range¶
For i = 1 To Range.Count¶
If Range(i).Interior.Color = criteriaColor.Interior.Color Then¶
SumIfColor = Application.Sum(SumIfColor, sum_range(i))¶
If IsError(SumIfColor) Then Exit Function¶
End If¶
Next i¶
End Function¶
4. To count or sum using the font color instead of the cell’s fill color,
change the code wherever it says Interior to Font.
5. Type the function(s) into a cell, following the same syntax as these two
examples:
=COUNTIFCOLOR(RangeToCheck, CellWithColor)
=SUMIFCOLOR(RangeToCheck, CellWithColor, RangeToSum)
Exl
Search JabSto ::




Custom Search