Want to change color based on Text, NOT using Conditional Formatin
 

I'm looking to change the color of numbers based on the text beside the column.
Then I need to sum up the values based on those colors.

I started things by using a Conditional Format, but that won't work. I
believe I have the code for the rest of this project though.

Also I'm looking through the Visual Basic Editor, there is a listing of all
the functions you can use, but no real detail or what they do. Any
information on where to find that? I could do this on my own if I knew more
about the calls to Excel.

Want to change color based on Text, NOT using Conditional Formatin
 

Here's a little something to change color based on cell value in the
current selection. You can then write a little code to sum up the
number of cells with an interior color index of a certain color. You
could even do that with a UDF.
Sub foofer()
Dim r As Range
For Each r In Selection
Select Case r.Text
Case "Fred"
r.Interior.ColorIndex = 6
Case "Tom"
r.Interior.ColorIndex = 4
Case Else
r.Interior.ColorIndex = xlNone
End Select
Next r
End Sub
Mitch wrote:
I'm looking to change the color of numbers based on the text beside the column.
Then I need to sum up the values based on those colors.

I started things by using a Conditional Format, but that won't work. I
believe I have the code for the rest of this project though.

Also I'm looking through the Visual Basic Editor, there is a listing of all
the functions you can use, but no real detail or what they do. Any
information on where to find that? I could do this on my own if I knew more
about the calls to Excel.

Want to change color based on Text, NOT using Conditional Form
 

Thanks but that's a sub snippet. I'm not just there yet when it comes to
using the vba with Excel, I need it to automatically change the color of 2
cells, the CDN, and then the 33 value afterwards. I've already adjusted the
code to change the Font color not the cell color. I just need to figure out
how to get the neighbour cell to change. I might make it a function, and have
it called in the neighbouring cell and have it change the 2 cells to the left
of it.
I get ?NAME with this. :(

I figure I could call the function
=ColChange(A1,A2)

Except it doesn't work.
Function ColChange(r1 As Range, r2 As Range)
Dim r1 As Range

Select Case r1.Text
Case "USD"
r1.Font.ColorIndex = 5
r2.Font.ColorIndex = 5
Case "CDN"
r1.Font.ColorIndex = 3
r2.Font.ColorIndex = 3
Case Else
r1.Font.ColorIndex = xlNone
r2.Font.ColorIndex = xlNone
End Select
End Function



"JW" wrote:

Here's a little something to change color based on cell value in the
current selection. You can then write a little code to sum up the
number of cells with an interior color index of a certain color. You
could even do that with a UDF.
Sub foofer()
Dim r As Range
For Each r In Selection
Select Case r.Text
Case "Fred"
r.Interior.ColorIndex = 6
Case "Tom"
r.Interior.ColorIndex = 4
Case Else
r.Interior.ColorIndex = xlNone
End Select
Next r
End Sub
Mitch wrote:
I'm looking to change the color of numbers based on the text beside the column.
Then I need to sum up the values based on those colors.

I started things by using a Conditional Format, but that won't work. I
believe I have the code for the rest of this project though.

Also I'm looking through the Visual Basic Editor, there is a listing of all
the functions you can use, but no real detail or what they do. Any
information on where to find that? I could do this on my own if I knew more
about the calls to Excel.

Want to change color based on Text, NOT using Conditional Form
 

Ok, so my UDF isn't even working. I tried using yours, thing is I need 2
cells. I'm not familar enough with this to have the system move a cell over
and apply the same formating. I always get $value when I do the UDF in the
neighbouring cell. I'm getting the impression I'm going about some of this
the wrong way.

Do you have any good reference sites that have additional examples? (or know
how to include 2 cells? Like the current cell and the left to the current
one?)




"JW" wrote:

Here's a little something to change color based on cell value in the
current selection. You can then write a little code to sum up the
number of cells with an interior color index of a certain color. You
could even do that with a UDF.
Sub foofer()
Dim r As Range
For Each r In Selection
Select Case r.Text
Case "Fred"
r.Interior.ColorIndex = 6
Case "Tom"
r.Interior.ColorIndex = 4
Case Else
r.Interior.ColorIndex = xlNone
End Select
Next r
End Sub
Mitch wrote:
I'm looking to change the color of numbers based on the text beside the column.
Then I need to sum up the values based on those colors.

I started things by using a Conditional Format, but that won't work. I
believe I have the code for the rest of this project though.

Also I'm looking through the Visual Basic Editor, there is a listing of all
the functions you can use, but no real detail or what they do. Any
information on where to find that? I could do this on my own if I knew more
about the calls to Excel.