Hi there,
I hope you can help. Is it possible to count a cell if the shading is set
to say, red or some other colour. If so, how do you determine what to call
that colour.
(Excel 2003)
Thanks so much. Diana

Diana, have a look here for a way to do it
http://www.cpearson.com/excel/colors.htm

--
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

"Cowtoon" wrote in message
...
Hi there,
I hope you can help. Is it possible to count a cell if the shading is set
to say, red or some other colour. If so, how do you determine what to
call
that colour.
(Excel 2003)
Thanks so much. Diana

Paul,
That's a good read, but I don't know very much about VB. How do I implement
some of those functions/scripts.
If I coloured a cell red in a range and want to count it as a value of 1, I
don't know how to get the script to work.
Also ... how do I find the RGB values for the colours that Excel uses. This
is a no brainer in other office components.
Thanks for the response. Diana


"Paul B" wrote in message
...
Diana, have a look here for a way to do it
http://www.cpearson.com/excel/colors.htm

--
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

"Cowtoon" wrote in message
...
Hi there,
I hope you can help. Is it possible to count a cell if the shading is set
to say, red or some other colour. If so, how do you determine what to
call
that colour.
(Excel 2003)
Thanks so much. Diana

Diana, have a look at the section on that page, Returning The ColorIndex Of
A Cell, to get the value of the colors

And have a look here on Getting Started with Macros and User Defined
Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
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

"Cowtoon" wrote in message
...
Paul,
That's a good read, but I don't know very much about VB. How do I
implement
some of those functions/scripts.
If I coloured a cell red in a range and want to count it as a value of 1,
I
don't know how to get the script to work.
Also ... how do I find the RGB values for the colours that Excel uses.
This
is a no brainer in other office components.
Thanks for the response. Diana


"Paul B" wrote in message
...
Diana, have a look here for a way to do it
http://www.cpearson.com/excel/colors.htm

--
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

"Cowtoon" wrote in message
...
Hi there,
I hope you can help. Is it possible to count a cell if the shading is
set
to say, red or some other colour. If so, how do you determine what to
call
that colour.
(Excel 2003)
Thanks so much. Diana

Paul,
I've created macros in Word, so I thinking I could "record" a macro in Excel
(to at least get started), however, I don't know what to use as a
command/function to start with. That's what got me. If I could do one
manually, I could create the macro. I kind-a figured I'd have run a macro
or some other script to make this work, but I don't know what the function
or parameters for the function should be. I'll know the range and the
destination cell for the calculation, but don't know what to put after the
COUNTIF function for it to count, if the background is say, red.

Does that make sense to you?


"Paul B" wrote in message
...
Diana, have a look at the section on that page, Returning The ColorIndex Of
A Cell, to get the value of the colors

And have a look here on Getting Started with Macros and User Defined
Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
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

"Cowtoon" wrote in message
...
Paul,
That's a good read, but I don't know very much about VB. How do I
implement
some of those functions/scripts.
If I coloured a cell red in a range and want to count it as a value of 1,
I
don't know how to get the script to work.
Also ... how do I find the RGB values for the colours that Excel uses.
This
is a no brainer in other office components.
Thanks for the response. Diana


"Paul B" wrote in message
...
Diana, have a look here for a way to do it
http://www.cpearson.com/excel/colors.htm

--
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

"Cowtoon" wrote in message
...
Hi there,
I hope you can help. Is it possible to count a cell if the shading is
set
to say, red or some other colour. If so, how do you determine what to
call
that colour.
(Excel 2003)
Thanks so much. Diana

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

"Cowtoon" wrote in message
...
Paul,
I've created macros in Word, so I thinking I could "record" a macro in
Excel
(to at least get started), however, I don't know what to use as a
command/function to start with. That's what got me. If I could do one
manually, I could create the macro. I kind-a figured I'd have run a macro
or some other script to make this work, but I don't know what the function
or parameters for the function should be. I'll know the range and the
destination cell for the calculation, but don't know what to put after the
COUNTIF function for it to count, if the background is say, red.

Does that make sense to you?


"Paul B" wrote in message
...
Diana, have a look at the section on that page, Returning The ColorIndex
Of
A Cell, to get the value of the colors

And have a look here on Getting Started with Macros and User Defined
Functions
http://www.mvps.org/dmcritchie/excel/getstarted.htm


--
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

"Cowtoon" wrote in message
...
Paul,
That's a good read, but I don't know very much about VB. How do I
implement
some of those functions/scripts.
If I coloured a cell red in a range and want to count it as a value of
1,
I
don't know how to get the script to work.
Also ... how do I find the RGB values for the colours that Excel uses.
This
is a no brainer in other office components.
Thanks for the response. Diana


"Paul B" wrote in message
...
Diana, have a look here for a way to do it
http://www.cpearson.com/excel/colors.htm

--
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

"Cowtoon" wrote in message
...
Hi there,
I hope you can help. Is it possible to count a cell if the shading is
set
to say, red or some other colour. If so, how do you determine what to
call
that colour.
(Excel 2003)
Thanks so much. Diana