Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color of text relating to a formula
Can I make a formula to add or not to add based on the color of the text in a
cell? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color of text relating to a formula
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color of text relating to a formula
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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color of text relating to a formula
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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color of text relating to a formula
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? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Color of text relating to a formula
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Relating numerals as text | Excel Discussion (Misc queries) | |||
Formula problems relating to Format | Excel Discussion (Misc queries) | |||
Cond. format text color based on another's text color | Excel Discussion (Misc queries) | |||
Relating text to a value | Excel Worksheet Functions | |||
Relating text to a value | Excel Worksheet Functions |