To put in this function, from your workbook right-click the workbook's icon
and pick View Code. This icon is at the top-left of the spreadsheet this
will open the VBA editor, in Project Explorer click on your workbook name,
if you don't see it press CTRL + r to open the Project Explorer, then go to
insert, module, and paste the code in the window that opens on the right
hand side, press Alt and Q to close this window and go back to your
workbook. If you want to count the number of cells in A1:A10 with a
background color of red (3) put this in another cell,
=COUNTBYCOLOR(A1:A10,3,FALSE)
If you are using excel 2000 or newer you may have to change the macro
security settings to get this to work. To change the security settings go to
tools, macro, security, security level and set it to medium.
NOTE: This functions will not detect colors that are applied by Conditional
Formatting. They will read only the default colors of the cell and its
text. For information about returning colors in effect by conditional
formatting, see the Conditional Formatting Colors page here
http://www.cpearson.com/excel/CFColors.htm
Below is a list of the colors and there numbers
Function CountByColor(InRange As Range, _
WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Long
'Use like =COUNTBYCOLOR(A1:A10,3,FALSE) for background
'=COUNTBYCOLOR(A1:A10,3,TRUE) for fonts
' This function return the number of cells in InRange with
' a background color, or if OfText is True a font color,
' equal to WhatColorIndex.
'
Dim Rng As Range
Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
CountByColor = CountByColor - _
(Rng.Font.ColorIndex = WhatColorIndex)
Else
CountByColor = CountByColor - _
(Rng.Interior.ColorIndex = WhatColorIndex)
End If
Next Rng
End Function
Color Index Color Name
1 Black
2 White
3 Red
4 Bright Green
5 Blue
6 Yellow
7 Pink
8 Turquoise
9 Dark Red
10 Green
11 Dark Blue
12 Dark Yellow
13 Violet
14 Teal
15 Gray-25%
16 Gray-50%
17 Periwinkle
18 Plum
19 Ivory
20 Light Turquoise
21 Dark Purple
22 Coral
23 Ocean Blue
24 Ice Blue
25 Dark Blue
26 Pink
27 Yellow
28 Turquoise
29 Violet
30 Dark Red
31 Teal
32 Blue
33 Sky Blue
34 Light Turquoise
35 Light Green
36 Light Yellow
37 Pale Blue
38 Rose
39 Lavender
40 Tan
41 Light Blue
42 Aqua
43 Lime
44 Gold
45 Light Orange
46 Orange
47 Blue-Gray
48 Gray-40%
49 Dark Teal
50 Sea Green
51 Dark Green
52 Olive Green
53 Brown
54 Plum
55 Indigo
56 Gray-80%
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
