weird macro

[Guest]

Hi all.
I realize that this will probably be a weird macro, but I want to sum a
group of cells that are not in series.
I.e., it'll all be in the same column, but there will be jumps between rows.
Some of the row spacing will be quite far, and others can be in series.
It seems to me that if I use some type of formatting-- such as color-- I
should be able to use an if, or sumif function to sum all the cells that I
need to sum.
I'm doing this because some of the tests that I've run are giving me mixed
numbers, and I want to go back and make sure that I did not miss any of the
cells I need to include in my sum/tally function.
The question I have is:
Can I make a macro to look at the color of the cell, and if it's the color
I've chosen, include that in the range of cells I want totalled?
If so, how would I write that?
Here's how I'm seeing it in my mind right now.

Sub ColorSum()
Dim color as CellFormat
if (color = 12) ' where 12 is the color of my choosing
sum(range) ' where range is the range of cells to be summed.
end if
End Sub

Simplistic, and wrong, I'm sure, but I want something to that effect.

 

[Chip Pearson]

See http://www.cpearson.com/excel/colors.htm for example code of a
SumByColor function.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Rodrigo Ferreira]

Try something like this:

Function SumColor(RangeSum As Range, cellColor As Range)
For Each objCell In RangeSum
If objCell.Font.ColorIndex = cellColor.Font.ColorIndex Then
SumValue = SumValue + objCell.Value
End If
Next
SumColor = SumValue
End Function

RangeSum = range that you want to sum
cellColor = cell that have the color formating

 

[Guest]

hmmm....
I'm trying really hard to not laugh. as a result of my surprise that someone
had already worked it out....
Chip....
Thank you.
I've printed out the page you gave me the link to.
I see the section you've called
"Summing the Values of Cells With a Specific Color."
Thanks again for your speedy reply.
Enjoy your weekend.

 

[Guest]

ok.... now I have to ask one of those stupid type questions.
For reasons beyond my understanding, I cannot get it to work.
But something that I've noticed is that other "functions" that I've obtained
from other sources do not work either.
This then leads me to wonder if there is some security function in Excel
2007 that prevents me from accessing these.
And yes, I've turned off every security function-- those that keep me from
accessing macros-- that I could find.

 

[Guest]

Rodrigo,
Thanks for your input.
However, I'm having the same problem that I've gottne with Chip's version.
When I enter the equation:
=SumColor(range,CellColor) I get the #NAME error.
where the CellColor is an integer.
When I check the calculation steps excel does not recognize the function.
Best Regards.
Thanks.

 

[Chip Pearson]

The VBA code must be place in a standard code module (in the VBA editor,
choose Insert menu, then Module), not a Sheet module or the ThisWorkbook
module.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

Ok, let's try this again.... I got some error stating that the service was
not available.
I've placed the code in one of the modules for my personal.xlsb file.
I learned that lesson a long time ago.

 

[Guest]

Chip,
I've done some digging, and I need to ask some questions so that I can tell
if I'm "getting it" or not.
These "functions" are User Defined Functions (UDF), correct?
If so, are there different types of UDF's, in that some are accessible
directly within Excel, and others can only be created in Visual Basic--
outside of Excel?
I found some documentation that describes UDF's as being disabled by default.
And that the only way to enable them is to go into what appears to be the
registry.

However, from what else I can only assume-- is that these should work
without having to dig so deep into the computers bowels, correct?

 

[Dave Peterson]

If the UDF is in another workbook, then you'll have to tell excel where to find
it.

One way is:
=personal.xlsb!sumbycolor(...)

I'm not sure what that message means.

 

[Dave Peterson]

I think you're very close!

This UDF lives in your personal.xlsb file--it's not created using VB (but you
did use VBA).

 

[Guest]

Ok,
I tried using:
=personal.xlsb!sumbycolor(range,CellColor)
and receive the following:
#VALUE
Which appears to get me one step closer.
However, I then tried a simpler udf-- the far2cel function in chapter 1 of
the WROX book "VBA for Excel2007."
It returned a 0.
I tried 5 or 6 different numbers, and all returned zeros.
I then went in and placed parenthesis around the major components, thinking
that the equation needed some further definition.

Any more ideas as to what I could be missing?
Again, thank you.

 

[Guest]

A few minutes later than my last post.
I found it.
After my #VALUE response, I went back in, and tried using the option for the
font color--
personal.xlsb!sumbycolor(range,FontColor,true) and got the answer I sought.
I'll test this further come Tuesday.
Enjoy your weekend-- incase you actually see this before Tuesday.
Thank you.

 

[Dave Peterson]

Let me be the first to say "woohoo!" <vbg>.

Congratulations. Now that you know how this stuff works, you've opened yourself
up for creating lots and lots of these functions. You may never see the light
of day again!

 

[Guest]

Thanks.
Ok, back at work now, and have tried it here.
The font color option works fine, but I'm unable to get the cell color
option to work.
I keep getting the #NUM error.
The help file says that I'm either getting too small/large a number, using
an iteration function-- I went into options, on the formula bar, and changed
that accordingly.
The last one was that I'm unsing a function that requires a number, and I
wasn't giving it a number.
I've looked more closely there, and found nothing that's obvious.
As I consider this farther, I'm wondering about the cell color number being
questionable.
It's an 8 digit integer, and for the life of me, I cannot understand why
excel has changed the single to triple digit color values to much much higher
ones.
Eg., I chose a simple color-- blue. Since I could not remember teh numeric
value for blue, I clicked on record macro, and then changed the color to
blue.
I then went into VBA editor, and looked at the color number.
It's 8 digits. I then took an earlier version of the blue macro that I made
earlier this year-- under office 2000. That "blue macro" is a number 5. I
tried using it, and got a black cell.
Part of me wants to use a couple of expletives here, but I know it's
pointless. I don't understand what was wrong with the former number set for
colors.
Further assistance at this point would be appreciated.
Thanks.

 

[Guest]

Hello again.
I've found the old (Excel 2000) blue macro-- color #5-- that I'd made and
got it to work.
The SumByColor function now appears to work well.
This issue that I mentioned however brings up a question.
Do these types of functions NOT recognize RGB/true colors?
I have to ask this because the present form of the color menus in excel
doesn't appear to show, or allow the user to determine the color number as
easily as before.
Then, for future reference, is there a way to get this UDF to recognize
RGB/true colors?
Thanks.

Thanks.
Ok, back at work now, and have tried it here.
The font color option works fine, but I'm unable to get the cell color
option to work.
I keep getting the #NUM error.
The help file says that I'm either getting too small/large a number, using
an iteration function-- I went into options, on the formula bar, and changed
that accordingly.
The last one was that I'm unsing a function that requires a number, and I
wasn't giving it a number.
I've looked more closely there, and found nothing that's obvious.
As I consider this farther, I'm wondering about the cell color number being
questionable.
It's an 8 digit integer, and for the life of me, I cannot understand why
excel has changed the single to triple digit color values to much much higher
ones.
Eg., I chose a simple color-- blue. Since I could not remember teh numeric
value for blue, I clicked on record macro, and then changed the color to
blue.
I then went into VBA editor, and looked at the color number.
It's 8 digits. I then took an earlier version of the blue macro that I made
earlier this year-- under office 2000. That "blue macro" is a number 5. I
tried using it, and got a black cell.
Part of me wants to use a couple of expletives here, but I know it's
pointless. I don't understand what was wrong with the former number set for
colors.
Further assistance at this point would be appreciated.
Thanks.

 

[Dave Peterson]

Read Chip's page once more.

Notice how he uses .ColorIndex in the UDF, not .color

Chip has notes how .color relates to the .colorindex at the top.