Adding sums only in certain colored cells
 

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

Adding sums only in certain colored cells
 

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

Adding sums only in certain colored cells
 

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

Adding sums only in certain colored cells
 

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

Adding sums only in certain colored cells
 

I keep getting the #NAME? error. Maybe I'm copying and pasting the wrong
info on the visual basic code section? I pasted the following:

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

Then, I used the following formula =sumbycolor(H87:H89, 35, FALSE) on my
worksheet.

Any ideas?
Thanks so much for your help.
VK





"Gord Dibben" wrote:

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

Adding sums only in certain colored cells
 

To where are you pasting the code?

It must go into a General Module in the workbook you are using.

If in another workbook like Personal.xls you must preface with the file name.

=Personal.xls!sumbycolor(H87:H89, 35, FALSE)


Gord Dibben MS Excel MVP

On Thu, 28 Dec 2006 12:22:00 -0800, vkauahi
wrote:

I keep getting the #NAME? error. Maybe I'm copying and pasting the wrong
info on the visual basic code section? I pasted the following:

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

Then, I used the following formula =sumbycolor(H87:H89, 35, FALSE) on my
worksheet.

Any ideas?
Thanks so much for your help.
VK





"Gord Dibben" wrote:

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

Adding sums only in certain colored cells
 

I figured it out. Thanks so much for all your help.
VK

"Gord Dibben" wrote:

To where are you pasting the code?

It must go into a General Module in the workbook you are using.

If in another workbook like Personal.xls you must preface with the file name.

=Personal.xls!sumbycolor(H87:H89, 35, FALSE)


Gord Dibben MS Excel MVP

On Thu, 28 Dec 2006 12:22:00 -0800, vkauahi
wrote:

I keep getting the #NAME? error. Maybe I'm copying and pasting the wrong
info on the visual basic code section? I pasted the following:

Function SumByColor(InRange As Range, WhatColorIndex As Integer, _
Optional OfText As Boolean = False) As Double
'
' This function return the SUM of the values of cells in
' InRange with a background color, or if OfText is True a
' font color, equal to WhatColorIndex.
'
Dim Rng As Range
Dim OK As Boolean

Application.Volatile True
For Each Rng In InRange.Cells
If OfText = True Then
OK = (Rng.Font.ColorIndex = WhatColorIndex)
Else
OK = (Rng.Interior.ColorIndex = WhatColorIndex)
End If
If OK And IsNumeric(Rng.Value) Then
SumByColor = SumByColor + Rng.Value
End If
Next Rng

End Function

Then, I used the following formula =sumbycolor(H87:H89, 35, FALSE) on my
worksheet.

Any ideas?
Thanks so much for your help.
VK





"Gord Dibben" wrote:

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