Color of text relating to a formula

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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-click>Insert>Module.

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
 
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
 
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 Tools>Macro>Macros.

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