counting cells based on formatting

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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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 Insert>Module

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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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 Insert>Module
>
> 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" <[email protected]> wrote in message
> news:[email protected]...
> >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" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > 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" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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 Insert>Module
>>
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> >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" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > 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" <[email protected]> wrote in message
>> >> >> news:[email protected]...
>> >> >> > 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
---

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