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.

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)


"SteveDB1" wrote in message
...
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.

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.


"Chip Pearson" wrote:

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)


"SteveDB1" wrote in message
...
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.

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.


"Chip Pearson" wrote:

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)


"SteveDB1" wrote in message
...
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.

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)

"SteveDB1" wrote in message
...
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.


"Chip Pearson" wrote:

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)


"SteveDB1" wrote in message
...
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.

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.



"Chip Pearson" wrote:

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)

"SteveDB1" wrote in message
...
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.


"Chip Pearson" wrote:

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)


"SteveDB1" wrote in message
...
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.

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.



SteveDB1 wrote:

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.

"Chip Pearson" wrote:

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)

"SteveDB1" wrote in message
...
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.


"Chip Pearson" wrote:

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)


"SteveDB1" wrote in message
...
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.




--

Dave Peterson

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?


"Chip Pearson" wrote:

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)

"SteveDB1" wrote in message
...
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.


"Chip Pearson" wrote:

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)


"SteveDB1" wrote in message
...
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.

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



SteveDB1 wrote:

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?

"Chip Pearson" wrote:

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)

"SteveDB1" wrote in message
...
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.


"Chip Pearson" wrote:

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)


"SteveDB1" wrote in message
...
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.




--

Dave Peterson

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

--

Rodrigo Ferreira
Regards from Brazil


"SteveDB1" escreveu na mensagem
...
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.

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.

"Rodrigo Ferreira" wrote:

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

--

Rodrigo Ferreira
Regards from Brazil


"SteveDB1" escreveu na mensagem
...
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.