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

I have some similar but different questions about using color and conditional
formatting in Excel.

1st, it looks like Excel 2007 may finally allow users to format cells by RGB
code but I havent yet figured out how exactly to make it work in the MS
online Excel 2007. I find MS elusive on the subject. Like €œDiana€�, I do not
want to go to the €œextreme€� of learning and using VB if possible. Right now,
Im using Excel 2003.

Im a holographer who wrote a small BASIC program in 1979 for following a
single ray through the hologram design process. It starts with a small number
of knowns about the finished hologram and ends with the correct optical
setups for recording that hologram.

I used CPM based Supercalc to enhance the program, moved it to DOS based
Lotus 123 and it now functions in Excel 2003. As part of the design process,
the current version allows a user to previsualize the colors of the final
hologram from each of three eye positions perpendicular to the top, middle,
and bottom of the hypothetical hologram. It allows the user to €œsee€� the
colors the eye will see at each of those three positions at the top, middle,
and bottom of the hologram.

The spreadsheet generates the colors as wavelengths in nanometers and
converts the values to text in a vlookup table so the user has both numerical
wavelength values and textual color representations. See example below:

Eye Red
Light red
Orange


Light red
Eye Orange
Yellow


Orange
Yellow
Eye Yellow/green


I have found a couple of very nice little freeware programs online which
will convert wavelength values to RGB values. I can make a lookup table for
the 430 visible integer wavelengths which will give their respective RGB
values.

MY PROBLEM: I cant figure out how to use those RGB values to conditionally
format my cell background colors so I can have a €œtrue€� color representation
in each cell in addition to the text I now use. I originally requested this
as a feature from MS in the early 90s€¦

Here is the URL for one of the sites:
http://www.efg2.com/Lab/ScienceAndEn...ng/Spectra.htm The aforementioned
zip file is at the bottom of the URL.

One of these days, Ill bite the bullet and figure out how to convert the
xls to Mathcad and drive an interactive diagram ;-)

Regards,
Joe Burns

See my response in your other thread

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Joe Burns" wrote in message
...
I have some similar but different questions about using color and
conditional
formatting in Excel.

1st, it looks like Excel 2007 may finally allow users to format cells by
RGB
code but I haven't yet figured out how exactly to make it work in the MS
online Excel 2007. I find MS elusive on the subject. Like "Diana", I do
not
want to go to the "extreme" of learning and using VB if possible. Right
now,
I'm using Excel 2003.

I'm a holographer who wrote a small BASIC program in 1979 for following a
single ray through the hologram design process. It starts with a small
number
of knowns about the finished hologram and ends with the correct optical
setups for recording that hologram.

I used CPM based Supercalc to enhance the program, moved it to DOS based
Lotus 123 and it now functions in Excel 2003. As part of the design
process,
the current version allows a user to previsualize the colors of the final
hologram from each of three eye positions perpendicular to the top,
middle,
and bottom of the hypothetical hologram. It allows the user to "see" the
colors the eye will see at each of those three positions at the top,
middle,
and bottom of the hologram.

The spreadsheet generates the colors as wavelengths in nanometers and
converts the values to text in a vlookup table so the user has both
numerical
wavelength values and textual color representations. See example below:

Eye Red
Light red
Orange


Light red
Eye Orange
Yellow


Orange
Yellow
Eye Yellow/green


I have found a couple of very nice little freeware programs online which
will convert wavelength values to RGB values. I can make a lookup table
for
the 430 visible integer wavelengths which will give their respective RGB
values.

MY PROBLEM: I can't figure out how to use those RGB values to
conditionally
format my cell background colors so I can have a "true" color
representation
in each cell in addition to the text I now use. I originally requested
this
as a feature from MS in the early 90s.

Here is the URL for one of the sites:
http://www.efg2.com/Lab/ScienceAndEn...ng/Spectra.htm The
aforementioned
zip file is at the bottom of the URL.

One of these days, I'll bite the bullet and figure out how to convert the
xls to Mathcad and drive an interactive diagram ;-)

Regards,
Joe Burns