Sum or Count cells that have a specified Fill Color

[Seanzz]

Hi everyone,
I found a VBA code to “Sum or Count cells that have a specified Fill Colorâ€
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
But “the changing of a cells fill color will not cause the Custom Function
to recalculateâ€
My question is how can I modify the code so the Function will recalculate
the range of cells once they are updated.

 

[Steven Cheng[MSFT]]

Hello Seanzz,

From your description, you want to do some programmatic summation and
calcuation on some range in excel(with same backcolor) and also want to
make them update when there is any input change, correct?

I've read the article you mentioned, yes, that article provide a function
that can help calculate count and summation of the certain cells(in a
range), but it is up to you that when do you call this function.

Based on my experience, if you want to add code to automatically do some
updating when there is any changes in certain cells in excel. The
reasonable means is to define a named range for those certain cells block
you want to take care. Then, you can hook the "change" event of that
namedrange. Here is the document of this event, you can have a look on this:

#NamedRange.Change Event
http://msdn2.microsoft.com/en-us/library/microsoft.office.tools.excel.namedr
ange.change(VS.80).aspx

Here are some other webarticles that introduce some tips about excel
namedranges:

http://www.ozgrid.com/News/excel-named-ranges.htm

Hope this helps you some.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================


This posting is provided "AS IS" with no warranties, and confers no rights.





--------------------

From: =?Utf-8?B?U2Vhbnp6?= <[email protected]>
Subject: Sum or Count cells that have a specified Fill Color
Date: Mon, 1 Oct 2007 10:16:01 -0700

Hi everyone,
I found a VBA code to “Sum or Count cells that have a specified Fill Color�
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
But “the changing of a cells fill color will not cause the Custom Function
to recalculate�

My question is how can I modify the code so the Function will recalculate

 

[Seanzz]

Hi Steven,

Thanks for your reply.

I also post a topic in ozgrid.com, they told me that filling color in a cell
will not have an event occurring, one option is to use event based on another
cell's value. However it is not what I want.
So I decide to run the Macro every time I open the excel file, then all the
cells will be recalculated. Check the link for more information:
Automatically Run Excel Macros Upon Opening Excel Workbook/Files. Auto Run
Excel VBA Macro Code
http://www.ozgrid.com/VBA/auto-run-macros.htm

Your information definitely helps too! Thanks again!

Best regards
Sean

 

[Jialiang Ge [MSFT]]

Hello Sean,

Thank you for sharing the solution here.

Yes, you are right. the Change event in Excel is not able to capture the
change of cell's fill color by design, it is only able to catch the change
of data. I feel sorry if the behavior does not fit your issue.

I am glad that you have found a workaround (do the recalculation in the
Workbook open event). We have recorded the solution for future reference.

Thank you again for your sharing. If you have any other concern or need
anything else, please feel free to let me know.

Sincerely,
Jialiang Ge ([email protected], remove 'online.')
Microsoft Online Community Support

=================================================
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from your issue.
=================================================
This posting is provided "AS IS" with no warranties, and confers no rights.