Re: Adding sums only in certain colored cells

Tech-Archive recommends: Fix windows errors by optimizing your registry

Copy Chip's SumByColor Function to a general module in your workbook.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to Excel window.

Enter the formula as such....=SumByColor(cellref,indexnumber,true or false)

Indexnumber is that of the background color or the font color.

If looking to sum by BG color use FALSE as argument.

If looking to sum by font color use TRUE as argument.

For a list of colorindex numbers see David McRitchie's site

http://www.mvps.org/dmcritchie/excel/colors.htm


Gord

On Fri, 22 Dec 2006 12:42:01 -0800, vkauahi <vkauahi@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

It is manually colored, now I have to figure out the VBA bit...(new to excel)
Thanks so much for your help. I'm so glad I found this site.
VK

"Gord Dibben" wrote:

If cells are colored due to Conditional Formatting, use the same criteria(on)
that was used to format them.

If manually colored you will need VBA to SUM them by color.

See Chip Pearson's site for Functions.

http://www.cpearson.com/excel/colors.htm


Gord Dibben MS Excel MVP

On Fri, 22 Dec 2006 11:51:01 -0800, vkauahi <vkauahi@xxxxxxxxxxxxxxxxxxxxxxxxx>
wrote:

I don't know if this is possible, but would greatly help me out if it is.
I want to know if it's possible to add the sums of certain color cells only.
For example, when I color code my sums green, that means I have it in my
account. Other colors mean other things...I want it to add it up
automatically when I change it to green. Hope that makes sense. Is this
possible? Any help would be greatly appreciated.
Thank!
VK



Gord Dibben MS Excel MVP
.

Relevant Pages

  • Re: funcres(funcres.xla)
    ... If you use the functions in the analysis toolpak in your workbook, ... Analysis TookPak - VBA ... David wrote: ...
    (microsoft.public.excel.programming)
  • Re: Inserting Page Breaks
    ... First...create a backup copy of your original workbook. ... You can also assign this macro to a button or a shortcut key combo. ... Need help with VBA? ... See David McRitchie's site on "getting started" with VBA ...
    (microsoft.public.excel.misc)
  • Re: Problem with Desktop Access(VB) manipulating Pocket Excel Files
    ... You might also want to have Access VBA save a copy of the Workbook just ... > Thanks for your thoughts David. ... > they were saved as Pocket Excel workbooks?? ...
    (microsoft.public.pocketpc)
  • Re: Sum according to font colour
    ... If you are new to VBA visit David McRitchie's site on "getting started" with ... Formatting) you can use the same criteria to perform summations without VBA. ... Is there a way to sum a range of values according to the colour of the ...
    (microsoft.public.excel)
  • Re: Pop Ups
    ... If it is A password then you can save the workbook as password ... or else use VBA to write a message box in the workbook_Open Event. ... msgbox "This is my Text" ... David ...
    (microsoft.public.excel.programming)