Hello.

I am hoping you can answer an excel question for me. Im using excel to
track a schedule. Im not sure if it was the best program to use but I dont
want to switch now. Is there a formula that will count how many boxes are
shaded a particular color? For instance every blue shaded one is equal to 15
minutes.

Thanks.

Hi!

A "best practice" would be to craft a formula based on the logic of why the
cells are colored rather than count cells that are colored.

But to answer your question, see this:

http://xldynamic.com/source/xld.ColourCounter.html

Using the count color method has a "bug". Changing a cells color does not
trigger a calculation so the resultant formula will not update a color
change until a calculation is triggered either manually or by some other
event.

Biff

"dwae2000" wrote in message
...
Hello.

I am hoping you can answer an excel question for me. I'm using excel to
track a schedule. I'm not sure if it was the best program to use but I
don't
want to switch now. Is there a formula that will count how many boxes are
shaded a particular color? For instance every blue shaded one is equal to
15
minutes.

Thanks.

Using the count color method has a "bug".

Just to clarify, the code and method do not contain a bug. The "bug" is the
way Excel handles this!

Biff

"Biff" wrote in message
...
Hi!

A "best practice" would be to craft a formula based on the logic of why
the cells are colored rather than count cells that are colored.

But to answer your question, see this:

http://xldynamic.com/source/xld.ColourCounter.html

Using the count color method has a "bug". Changing a cells color does not
trigger a calculation so the resultant formula will not update a color
change until a calculation is triggered either manually or by some other
event.

Biff

"dwae2000" wrote in message
...
Hello.

I am hoping you can answer an excel question for me. I'm using excel to
track a schedule. I'm not sure if it was the best program to use but I
don't
want to switch now. Is there a formula that will count how many boxes
are
shaded a particular color? For instance every blue shaded one is equal
to 15
minutes.

Thanks.

Thanks for the response. I wish I understood how to do it! I assume it is
using VB, which I unfortunately know little about.

Thanks for your time.

"Biff" wrote:

Hi!

A "best practice" would be to craft a formula based on the logic of why the
cells are colored rather than count cells that are colored.

But to answer your question, see this:

http://xldynamic.com/source/xld.ColourCounter.html

Using the count color method has a "bug". Changing a cells color does not
trigger a calculation so the resultant formula will not update a color
change until a calculation is triggered either manually or by some other
event.

Biff

"dwae2000" wrote in message
...
Hello.

I am hoping you can answer an excel question for me. I'm using excel to
track a schedule. I'm not sure if it was the best program to use but I
don't
want to switch now. Is there a formula that will count how many boxes are
shaded a particular color? For instance every blue shaded one is equal to
15
minutes.

Thanks.

Ok, if you want, I can walk you through it step-by-step. Just let me know
that you're still following this thread.

Biff

"dwae2000" wrote in message
...
Thanks for the response. I wish I understood how to do it! I assume it
is
using VB, which I unfortunately know little about.

Thanks for your time.

"Biff" wrote:

Hi!

A "best practice" would be to craft a formula based on the logic of why
the
cells are colored rather than count cells that are colored.

But to answer your question, see this:

http://xldynamic.com/source/xld.ColourCounter.html

Using the count color method has a "bug". Changing a cells color does not
trigger a calculation so the resultant formula will not update a color
change until a calculation is triggered either manually or by some other
event.

Biff

"dwae2000" wrote in message
...
Hello.

I am hoping you can answer an excel question for me. I'm using excel
to
track a schedule. I'm not sure if it was the best program to use but I
don't
want to switch now. Is there a formula that will count how many boxes
are
shaded a particular color? For instance every blue shaded one is equal
to
15
minutes.

Thanks.

I would love any help I can get.

Thanks.

"Biff" wrote:

Ok, if you want, I can walk you through it step-by-step. Just let me know
that you're still following this thread.

Biff

"dwae2000" wrote in message
...
Thanks for the response. I wish I understood how to do it! I assume it
is
using VB, which I unfortunately know little about.

Thanks for your time.

"Biff" wrote:

Hi!

A "best practice" would be to craft a formula based on the logic of why
the
cells are colored rather than count cells that are colored.

But to answer your question, see this:

http://xldynamic.com/source/xld.ColourCounter.html

Using the count color method has a "bug". Changing a cells color does not
trigger a calculation so the resultant formula will not update a color
change until a calculation is triggered either manually or by some other
event.

Biff

"dwae2000" wrote in message
...
Hello.

I am hoping you can answer an excel question for me. I'm using excel
to
track a schedule. I'm not sure if it was the best program to use but I
don't
want to switch now. Is there a formula that will count how many boxes
are
shaded a particular color? For instance every blue shaded one is equal
to
15
minutes.

Thanks.

Ok........

Start Excel and open the file in question.

Open the VBE editor by hitting ALT F11
Open the Project Explorer by hitting CTRL R

In the Project Explorer pane look for your file. It will look like this:

VBAProject(your_filename.xls)

Select the VBAProject with your filename.
Right click and select InsertModule

An empty window will open on the right side. This is called a module. To be
more specific, this is a GENERAL MODULE.

Paste the code from this link into the module:

http://xldynamic.com/source/xld.ColourCounter.html#code

Copy the entire contents of the "gray box". Some of the code is comments but
that won't affect anything.

Add this line of code where noted: Application.Volatile

----------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

Application.Volatile '<-----add this line

If rng.Areas.Count 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If


Ok, now exit the VBE and return to your spreadsheet, click the X to close
the VBE.

To use this code to count cells that are a certain color you must first know
what the color index number is for the color of interest. You can find these
index numbers by filling some cells and then using this formula. Fill cell
A1 with any color then enter this formula in B1:

=ColorIndex(A1)

Now, to see the "bug" I noted in my other reply change the fill color of
cell A1. You'll notice that the result of the formula did not change. Now
press F9. This triggers a calculation. You'll notice that the formula result
has now changed.

Ok, now, to count the "blue" cells in the range A1:A10:

=SUMPRODUCT(--(COLORINDEX(A1:A10)=5))

So, that's it!

Just remember that changing a cells color does not trigger a calculation.
You either have to trigger a manual calculation by hitting function key F9
or wait until an event triggered calculation occurs.

It's for the above reason that I never use this method. I'd rather build a
formula based on the logic of WHY the cells are colored although sometimes
it's not so obvious WHY cells are certains colors!

Biff

"dwae2000" wrote in message
...
I would love any help I can get.

Thanks.

"Biff" wrote:

Ok, if you want, I can walk you through it step-by-step. Just let me know
that you're still following this thread.

Biff

"dwae2000" wrote in message
...
Thanks for the response. I wish I understood how to do it! I assume
it
is
using VB, which I unfortunately know little about.

Thanks for your time.

"Biff" wrote:

Hi!

A "best practice" would be to craft a formula based on the logic of
why
the
cells are colored rather than count cells that are colored.

But to answer your question, see this:

http://xldynamic.com/source/xld.ColourCounter.html

Using the count color method has a "bug". Changing a cells color does
not
trigger a calculation so the resultant formula will not update a color
change until a calculation is triggered either manually or by some
other
event.

Biff

"dwae2000" wrote in message
...
Hello.

I am hoping you can answer an excel question for me. I'm using
excel
to
track a schedule. I'm not sure if it was the best program to use
but I
don't
want to switch now. Is there a formula that will count how many
boxes
are
shaded a particular color? For instance every blue shaded one is
equal
to
15
minutes.

Thanks.

WoW! Thanks so much for all of your hard work and diligence and patience
with my question.

What a tremendous help.

"Biff" wrote:

Ok........

Start Excel and open the file in question.

Open the VBE editor by hitting ALT F11
Open the Project Explorer by hitting CTRL R

In the Project Explorer pane look for your file. It will look like this:

VBAProject(your_filename.xls)

Select the VBAProject with your filename.
Right click and select InsertModule

An empty window will open on the right side. This is called a module. To be
more specific, this is a GENERAL MODULE.

Paste the code from this link into the module:

http://xldynamic.com/source/xld.ColourCounter.html#code

Copy the entire contents of the "gray box". Some of the code is comments but
that won't affect anything.

Add this line of code where noted: Application.Volatile

----------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

Application.Volatile '<-----add this line

If rng.Areas.Count 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If


Ok, now exit the VBE and return to your spreadsheet, click the X to close
the VBE.

To use this code to count cells that are a certain color you must first know
what the color index number is for the color of interest. You can find these
index numbers by filling some cells and then using this formula. Fill cell
A1 with any color then enter this formula in B1:

=ColorIndex(A1)

Now, to see the "bug" I noted in my other reply change the fill color of
cell A1. You'll notice that the result of the formula did not change. Now
press F9. This triggers a calculation. You'll notice that the formula result
has now changed.

Ok, now, to count the "blue" cells in the range A1:A10:

=SUMPRODUCT(--(COLORINDEX(A1:A10)=5))

So, that's it!

Just remember that changing a cells color does not trigger a calculation.
You either have to trigger a manual calculation by hitting function key F9
or wait until an event triggered calculation occurs.

It's for the above reason that I never use this method. I'd rather build a
formula based on the logic of WHY the cells are colored although sometimes
it's not so obvious WHY cells are certains colors!

Biff

"dwae2000" wrote in message
...
I would love any help I can get.

Thanks.

"Biff" wrote:

Ok, if you want, I can walk you through it step-by-step. Just let me know
that you're still following this thread.

Biff

"dwae2000" wrote in message
...
Thanks for the response. I wish I understood how to do it! I assume
it
is
using VB, which I unfortunately know little about.

Thanks for your time.

"Biff" wrote:

Hi!

A "best practice" would be to craft a formula based on the logic of
why
the
cells are colored rather than count cells that are colored.

But to answer your question, see this:

http://xldynamic.com/source/xld.ColourCounter.html

Using the count color method has a "bug". Changing a cells color does
not
trigger a calculation so the resultant formula will not update a color
change until a calculation is triggered either manually or by some
other
event.

Biff

"dwae2000" wrote in message
...
Hello.

I am hoping you can answer an excel question for me. I'm using
excel
to
track a schedule. I'm not sure if it was the best program to use
but I
don't
want to switch now. Is there a formula that will count how many
boxes
are
shaded a particular color? For instance every blue shaded one is
equal
to
15
minutes.

Thanks.

We can thank Bob Phillips of xldynamic.com for posting the code!

Glad I could help.

Biff

"dwae2000" wrote in message
...
WoW! Thanks so much for all of your hard work and diligence and patience
with my question.

What a tremendous help.

"Biff" wrote:

Ok........

Start Excel and open the file in question.

Open the VBE editor by hitting ALT F11
Open the Project Explorer by hitting CTRL R

In the Project Explorer pane look for your file. It will look like this:

VBAProject(your_filename.xls)

Select the VBAProject with your filename.
Right click and select InsertModule

An empty window will open on the right side. This is called a module. To
be
more specific, this is a GENERAL MODULE.

Paste the code from this link into the module:

http://xldynamic.com/source/xld.ColourCounter.html#code

Copy the entire contents of the "gray box". Some of the code is comments
but
that won't affect anything.

Add this line of code where noted: Application.Volatile

----------------------------------------------------------
Dim cell As Range, row As Range
Dim i As Long, j As Long
Dim iWhite As Long, iBlack As Long
Dim aryColours As Variant

Application.Volatile '<-----add this line

If rng.Areas.Count 1 Then
ColorIndex = CVErr(xlErrValue)
Exit Function
End If


Ok, now exit the VBE and return to your spreadsheet, click the X to close
the VBE.

To use this code to count cells that are a certain color you must first
know
what the color index number is for the color of interest. You can find
these
index numbers by filling some cells and then using this formula. Fill
cell
A1 with any color then enter this formula in B1:

=ColorIndex(A1)

Now, to see the "bug" I noted in my other reply change the fill color of
cell A1. You'll notice that the result of the formula did not change. Now
press F9. This triggers a calculation. You'll notice that the formula
result
has now changed.

Ok, now, to count the "blue" cells in the range A1:A10:

=SUMPRODUCT(--(COLORINDEX(A1:A10)=5))

So, that's it!

Just remember that changing a cells color does not trigger a calculation.
You either have to trigger a manual calculation by hitting function key
F9
or wait until an event triggered calculation occurs.

It's for the above reason that I never use this method. I'd rather build
a
formula based on the logic of WHY the cells are colored although
sometimes
it's not so obvious WHY cells are certains colors!

Biff

"dwae2000" wrote in message
...
I would love any help I can get.

Thanks.

"Biff" wrote:

Ok, if you want, I can walk you through it step-by-step. Just let me
know
that you're still following this thread.

Biff

"dwae2000" wrote in message
...
Thanks for the response. I wish I understood how to do it! I
assume
it
is
using VB, which I unfortunately know little about.

Thanks for your time.

"Biff" wrote:

Hi!

A "best practice" would be to craft a formula based on the logic of
why
the
cells are colored rather than count cells that are colored.

But to answer your question, see this:

http://xldynamic.com/source/xld.ColourCounter.html

Using the count color method has a "bug". Changing a cells color
does
not
trigger a calculation so the resultant formula will not update a
color
change until a calculation is triggered either manually or by some
other
event.

Biff

"dwae2000" wrote in message
...
Hello.

I am hoping you can answer an excel question for me. I'm using
excel
to
track a schedule. I'm not sure if it was the best program to use
but I
don't
want to switch now. Is there a formula that will count how many
boxes
are
shaded a particular color? For instance every blue shaded one is
equal
to
15
minutes.

Thanks.

and in case you're interested ..

Here's a link from my archives to a sample illustrating Bob's ColorIndex &
it's usage (it's a great little starters' kit <g - full details inside):
http://savefile.com/files/3232462
CountCellsByFillColor_Using_BobPhillips_ColorIndex

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---