I have looked at C Pearsons and others stuff on Sumproduct background fill
colour But what I would like to do is Sum numbers contained in comments, from
cells with a certain background colour. Can it be done, if so how?

Hi Arran

Take a look at Bob Phillips site
http://xldynamic.com/source/xld.ColourCounter.html

--
Regards

Roger Govier


"Arran" wrote in message
...
I have looked at C Pearsons and others stuff on Sumproduct background
fill
colour But what I would like to do is Sum numbers contained in
comments, from
cells with a certain background colour. Can it be done, if so how?

Arron

Certainly it can be done, but you would need VBA code. The fill colour piece
would be fairly simple and the comment piece ok, providing the numbers are
on their own and not in random places around text. Below is an example UDF
that takes the range to be checked and a range (cell) with an example of the
background colour to test for. As I said this only works when the number is
the ONLY thing in the comment (This includes the default user name that
appears. obviously this could be coded out, but makes everything more
complicated

Function AddCommentsAndColours(rngToCheck As Range, chkColour As Range) As
Double
Dim mycell As Range
Dim colourNo As Integer
Dim dblFinal As Double
Application.Volatile True
colourNo = chkColour.Interior.ColorIndex
For Each mycell In rngToCheck
If mycell.Interior.ColorIndex = colourNo Then
If Not mycell.Comment Is Nothing Then
dblFinal = dblFinal + Val(mycell.Comment.Text)
End If
End If
Next mycell
AddCommentsAndColours = dblFinal
End Function

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Arran" wrote in message
...
I have looked at C Pearsons and others stuff on Sumproduct background fill
colour But what I would like to do is Sum numbers contained in comments,
from
cells with a certain background colour. Can it be done, if so how?

Hello again Roger, Hi Nick
Roger, thanks for your suggestion. I have found a number of sites dealing
with the Summing of cells based on colour. But I want to Sum the contents of
the comments in these coloured cells.

Nick, thanks for your reply particularly dealing with the comments part. I
will digest it and endeavour to implement. Watch this space.

Thanks again Roger


"Roger Govier" wrote:

Hi Arran

Take a look at Bob Phillips site
http://xldynamic.com/source/xld.ColourCounter.html

--
Regards

Roger Govier


"Arran" wrote in message
...
I have looked at C Pearsons and others stuff on Sumproduct background
fill
colour But what I would like to do is Sum numbers contained in
comments, from
cells with a certain background colour. Can it be done, if so how?

Hi Nick
Many thanks you for your reply. It would be great to say it all worked
swimmingly but I have to admit VBA is competly alien to me, I have no
experiance with it what so ever. I am goning to have to do some basic reading
up on Macros, so which of the numerous Excel sites in your opinion would be
the best one for a crash lesson on install macros.

regards
Arran


"Nick Hodge" wrote:

Arron

Certainly it can be done, but you would need VBA code. The fill colour piece
would be fairly simple and the comment piece ok, providing the numbers are
on their own and not in random places around text. Below is an example UDF
that takes the range to be checked and a range (cell) with an example of the
background colour to test for. As I said this only works when the number is
the ONLY thing in the comment (This includes the default user name that
appears. obviously this could be coded out, but makes everything more
complicated

Function AddCommentsAndColours(rngToCheck As Range, chkColour As Range) As
Double
Dim mycell As Range
Dim colourNo As Integer
Dim dblFinal As Double
Application.Volatile True
colourNo = chkColour.Interior.ColorIndex
For Each mycell In rngToCheck
If mycell.Interior.ColorIndex = colourNo Then
If Not mycell.Comment Is Nothing Then
dblFinal = dblFinal + Val(mycell.Comment.Text)
End If
End If
Next mycell
AddCommentsAndColours = dblFinal
End Function

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Arran" wrote in message
...
I have looked at C Pearsons and others stuff on Sumproduct background fill
colour But what I would like to do is Sum numbers contained in comments,
from
cells with a certain background colour. Can it be done, if so how?

Arran

There is a links page on my site at www.nickhodge.co.uk

Let us know specifically what it is you can't do and we'll try to help and
explain. May not be me, but someone will chime in. That's the idea of these
communities

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Arran" wrote in message
...
Hi Nick
Many thanks you for your reply. It would be great to say it all worked
swimmingly but I have to admit VBA is competly alien to me, I have no
experiance with it what so ever. I am goning to have to do some basic
reading
up on Macros, so which of the numerous Excel sites in your opinion would
be
the best one for a crash lesson on install macros.

regards
Arran


"Nick Hodge" wrote:

Arron

Certainly it can be done, but you would need VBA code. The fill colour
piece
would be fairly simple and the comment piece ok, providing the numbers
are
on their own and not in random places around text. Below is an example
UDF
that takes the range to be checked and a range (cell) with an example of
the
background colour to test for. As I said this only works when the number
is
the ONLY thing in the comment (This includes the default user name that
appears. obviously this could be coded out, but makes everything more
complicated

Function AddCommentsAndColours(rngToCheck As Range, chkColour As Range)
As
Double
Dim mycell As Range
Dim colourNo As Integer
Dim dblFinal As Double
Application.Volatile True
colourNo = chkColour.Interior.ColorIndex
For Each mycell In rngToCheck
If mycell.Interior.ColorIndex = colourNo Then
If Not mycell.Comment Is Nothing Then
dblFinal = dblFinal + Val(mycell.Comment.Text)
End If
End If
Next mycell
AddCommentsAndColours = dblFinal
End Function

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Arran" wrote in message
...
I have looked at C Pearsons and others stuff on Sumproduct background
fill
colour But what I would like to do is Sum numbers contained in
comments,
from
cells with a certain background colour. Can it be done, if so how?

Hi Nick
Had that crash lesson in VBA, managed to get it up & running eventually.
Picked up a lot of useful stuff along the way.

Many thanks for you help
Arran

"Nick Hodge" wrote:

Arran

There is a links page on my site at www.nickhodge.co.uk

Let us know specifically what it is you can't do and we'll try to help and
explain. May not be me, but someone will chime in. That's the idea of these
communities

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Arran" wrote in message
...
Hi Nick
Many thanks you for your reply. It would be great to say it all worked
swimmingly but I have to admit VBA is competly alien to me, I have no
experiance with it what so ever. I am goning to have to do some basic
reading
up on Macros, so which of the numerous Excel sites in your opinion would
be
the best one for a crash lesson on install macros.

regards
Arran


"Nick Hodge" wrote:

Arron

Certainly it can be done, but you would need VBA code. The fill colour
piece
would be fairly simple and the comment piece ok, providing the numbers
are
on their own and not in random places around text. Below is an example
UDF
that takes the range to be checked and a range (cell) with an example of
the
background colour to test for. As I said this only works when the number
is
the ONLY thing in the comment (This includes the default user name that
appears. obviously this could be coded out, but makes everything more
complicated

Function AddCommentsAndColours(rngToCheck As Range, chkColour As Range)
As
Double
Dim mycell As Range
Dim colourNo As Integer
Dim dblFinal As Double
Application.Volatile True
colourNo = chkColour.Interior.ColorIndex
For Each mycell In rngToCheck
If mycell.Interior.ColorIndex = colourNo Then
If Not mycell.Comment Is Nothing Then
dblFinal = dblFinal + Val(mycell.Comment.Text)
End If
End If
Next mycell
AddCommentsAndColours = dblFinal
End Function

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Arran" wrote in message
...
I have looked at C Pearsons and others stuff on Sumproduct background
fill
colour But what I would like to do is Sum numbers contained in
comments,
from
cells with a certain background colour. Can it be done, if so how?

Arran

You are most welcome...stick around and you learn tons of other stuff too

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Arran" wrote in message
...
Hi Nick
Had that crash lesson in VBA, managed to get it up & running eventually.
Picked up a lot of useful stuff along the way.

Many thanks for you help
Arran

"Nick Hodge" wrote:

Arran

There is a links page on my site at www.nickhodge.co.uk

Let us know specifically what it is you can't do and we'll try to help
and
explain. May not be me, but someone will chime in. That's the idea of
these
communities

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Arran" wrote in message
...
Hi Nick
Many thanks you for your reply. It would be great to say it all worked
swimmingly but I have to admit VBA is competly alien to me, I have no
experiance with it what so ever. I am goning to have to do some basic
reading
up on Macros, so which of the numerous Excel sites in your opinion
would
be
the best one for a crash lesson on install macros.

regards
Arran


"Nick Hodge" wrote:

Arron

Certainly it can be done, but you would need VBA code. The fill colour
piece
would be fairly simple and the comment piece ok, providing the numbers
are
on their own and not in random places around text. Below is an example
UDF
that takes the range to be checked and a range (cell) with an example
of
the
background colour to test for. As I said this only works when the
number
is
the ONLY thing in the comment (This includes the default user name
that
appears. obviously this could be coded out, but makes everything more
complicated

Function AddCommentsAndColours(rngToCheck As Range, chkColour As
Range)
As
Double
Dim mycell As Range
Dim colourNo As Integer
Dim dblFinal As Double
Application.Volatile True
colourNo = chkColour.Interior.ColorIndex
For Each mycell In rngToCheck
If mycell.Interior.ColorIndex = colourNo Then
If Not mycell.Comment Is Nothing Then
dblFinal = dblFinal + Val(mycell.Comment.Text)
End If
End If
Next mycell
AddCommentsAndColours = dblFinal
End Function

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
DTHIS
www.nickhodge.co.uk


"Arran" wrote in message
...
I have looked at C Pearsons and others stuff on Sumproduct background
fill
colour But what I would like to do is Sum numbers contained in
comments,
from
cells with a certain background colour. Can it be done, if so how?