Can I make a formula to add or not to add based on the color of the text in a
cell?

http://xldynamic.com/source/counting
--
Can''t hear the Rain ?
Then listen to it.


"pcsski" wrote:

Can I make a formula to add or not to add based on the color of the text in a
cell?

You missed part of the URL

Try this instead.

http://www.xldynamic.com/source/xld.ColourCounter.html


Gord Dibben MS Excel MVP

On Sun, 30 Sep 2007 06:52:01 -0700, The Rain
wrote:

http://xldynamic.com/source/counting
--
Can''t hear the Rain ?
Then listen to it.


"pcsski" wrote:

Can I make a formula to add or not to add based on the color of the text in a
cell?

Thanks. I went to that URL. I'm assuming I have to put that sample code into
excel. Where do I put it and do I have to make any changes to it? Just to
explain my situation, I have a column of numbers and at the bottom of the
column I want the formula to only add the green colored numbers.

"Gord Dibben" wrote:

You missed part of the URL

Try this instead.

http://www.xldynamic.com/source/xld.ColourCounter.html


Gord Dibben MS Excel MVP

On Sun, 30 Sep 2007 06:52:01 -0700, The Rain
wrote:

http://xldynamic.com/source/counting
--
Can''t hear the Rain ?
Then listen to it.


"pcsski" wrote:

Can I make a formula to add or not to add based on the color of the text in a
cell?

Copy all the code from the "Code Samples" box at bottom of page.

Alt + F11 to open the VB Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the code into that module.

Use one of the SUMPRODUCT formulas in a cell to sum the green colored text
cells.

Change the colorindex to 10 for green text.

i.e. =SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=10),A1:A100)

If you were summing on the background color use

=SUMPRODUCT(--(ColorIndex(A1:A100)=10),A1:A100)


Gord


On Sun, 30 Sep 2007 09:31:03 -0700, pcsski
wrote:

Thanks. I went to that URL. I'm assuming I have to put that sample code into
excel. Where do I put it and do I have to make any changes to it? Just to
explain my situation, I have a column of numbers and at the bottom of the
column I want the formula to only add the green colored numbers.

"Gord Dibben" wrote:

You missed part of the URL

Try this instead.

http://www.xldynamic.com/source/xld.ColourCounter.html


Gord Dibben MS Excel MVP

On Sun, 30 Sep 2007 06:52:01 -0700, The Rain
wrote:

http://xldynamic.com/source/counting
--
Can''t hear the Rain ?
Then listen to it.


"pcsski" wrote:

Can I make a formula to add or not to add based on the color of the text in a
cell?

Gord,

Thanks for your help. Very much appreciated! How do you know which number
goes with which color? For example 3 = red and 10 = green. Finally, the URL
page says the fomula does not automatically update and you have to do a
manual calc. So if I change an existing text to green in that column that was
black I have to manually caculate the formula? How do you do a manual calc?
Would it be just removing the formula from the cell and putting it back in?

Thanks again,
pcsski

"Gord Dibben" wrote:

Copy all the code from the "Code Samples" box at bottom of page.

Alt + F11 to open the VB Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the code into that module.

Use one of the SUMPRODUCT formulas in a cell to sum the green colored text
cells.

Change the colorindex to 10 for green text.

i.e. =SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=10),A1:A100)

If you were summing on the background color use

=SUMPRODUCT(--(ColorIndex(A1:A100)=10),A1:A100)


Gord


On Sun, 30 Sep 2007 09:31:03 -0700, pcsski
wrote:

Thanks. I went to that URL. I'm assuming I have to put that sample code into
excel. Where do I put it and do I have to make any changes to it? Just to
explain my situation, I have a column of numbers and at the bottom of the
column I want the formula to only add the green colored numbers.

"Gord Dibben" wrote:

You missed part of the URL

Try this instead.

http://www.xldynamic.com/source/xld.ColourCounter.html


Gord Dibben MS Excel MVP

On Sun, 30 Sep 2007 06:52:01 -0700, The Rain
wrote:

http://xldynamic.com/source/counting
--
Can''t hear the Rain ?
Then listen to it.


"pcsski" wrote:

Can I make a formula to add or not to add based on the color of the text in a
cell?

Easiest first...........a manual calc can be done by hitting the F9 key or
select any cell and hit F2 which puts you into edit mode then hit the ENTER key
to calculate the sheet.

Or double-click on a cell and hit ENTER.

This is the equivalent of removing the formula then putting it back.

To see the Colorindex numbers................see David's site at

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

Or run this macro which will add a new sheet and a list of the colorindexes
numbers with the colors.........store it in a module as you were shown in last
post then ToolsMacroMacros.

Select the macro by name then Run.

Sub ListColorIndexes()
Dim Ndx As Long
Sheets.Add
For Ndx = 1 To 56
Cells(Ndx, 1).Interior.ColorIndex = Ndx
Cells(Ndx, 2).Value = Hex(ThisWorkbook.Colors(Ndx))
Cells(Ndx, 3).Value = Ndx
Next Ndx
End Sub


Gord

On Sun, 30 Sep 2007 17:30:00 -0700, pcsski
wrote:

Gord,

Thanks for your help. Very much appreciated! How do you know which number
goes with which color? For example 3 = red and 10 = green. Finally, the URL
page says the fomula does not automatically update and you have to do a
manual calc. So if I change an existing text to green in that column that was
black I have to manually caculate the formula? How do you do a manual calc?
Would it be just removing the formula from the cell and putting it back in?

Thanks again,
pcsski

"Gord Dibben" wrote:

Copy all the code from the "Code Samples" box at bottom of page.

Alt + F11 to open the VB Editor.

CTRL + r to open the Project Explorer.

Select your workbook/project and right-clickInsertModule.

Paste the code into that module.

Use one of the SUMPRODUCT formulas in a cell to sum the green colored text
cells.

Change the colorindex to 10 for green text.

i.e. =SUMPRODUCT(--(ColorIndex(A1:A100,TRUE)=10),A1:A100)

If you were summing on the background color use

=SUMPRODUCT(--(ColorIndex(A1:A100)=10),A1:A100)


Gord


On Sun, 30 Sep 2007 09:31:03 -0700, pcsski
wrote:

Thanks. I went to that URL. I'm assuming I have to put that sample code into
excel. Where do I put it and do I have to make any changes to it? Just to
explain my situation, I have a column of numbers and at the bottom of the
column I want the formula to only add the green colored numbers.

"Gord Dibben" wrote:

You missed part of the URL

Try this instead.

http://www.xldynamic.com/source/xld.ColourCounter.html


Gord Dibben MS Excel MVP

On Sun, 30 Sep 2007 06:52:01 -0700, The Rain
wrote:

http://xldynamic.com/source/counting
--
Can''t hear the Rain ?
Then listen to it.


"pcsski" wrote:

Can I make a formula to add or not to add based on the color of the text in a
cell?