Microsoft Office Tutorials and References
In Depth Information
Applying SUM / COUNT by Color
These two functions work exactly like SUMIF and COUNTIF, but use the
criteria cell's background color instead of the cell’s value.
Follow these steps:
1. To first determine the color you would like to use, fill cell A1 (in a blank
workbook) with the desired color.
2. Place the function shown on the following page in a standard module.
Exl
Figure 20 – Sum/Count by Color
View the Appendix to learn how to store this procedure
in a Standard module.
Function GetColor(Rng As Range) As Long¶
GetColor = Rng(1).Interior.Color¶
End Function
3. Go back to the application interface, and type the following in cell B1:
=GetColor(A1)
This provides you with the color number to use in the VBA code below.
Option Explicit¶
' * * * * *¶
Function CountIfColor(ByVal Range As Range, _¶
ByVal criteriaColor As Range) As Variant¶
'Variable declaration¶
Dim Rng As Range¶
'Make it volatile - automatic calculation¶
Application.Volatile True¶
'Validations, only one area¶
If Range.Areas.Count > 1 Then¶
CountIfColor = CVErr(xlErrValue)¶
End If¶
Search JabSto ::




Custom Search