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.
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¶
3. Go back to the application interface, and type the following in cell B1:
This provides you with the color number to use in the VBA code below.
' * * * * *¶
Function CountIfColor(ByVal Range As Range, _¶
ByVal criteriaColor As Range) As Variant¶
Dim Rng As Range¶
'Make it volatile - automatic calculation¶
'Validations, only one area¶
If Range.Areas.Count > 1 Then¶
CountIfColor = CVErr(xlErrValue)¶