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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mitch - 19 Sep 2007 20:28 GMT

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.

Reply to this Message

JW - 19 Sep 2007 21:34 GMT

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

> 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.
[quoted text clipped - 6 lines]
> information on where to find that? I could do this on my own if I knew more
> about the calls to Excel.

Reply to this Message

Mitch - 20 Sep 2007 14:14 GMT

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

> 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
[quoted text clipped - 23 lines]
> > information on where to find that? I could do this on my own if I knew more
> > about the calls to Excel.

Reply to this Message

Mitch - 20 Sep 2007 20:30 GMT

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

Reply to this Message