UFD to change color based on string

[Guest]

ok, I keep getting =Name? and I'm not sure if this is even going to work.
Any help would be appreciated. Tried figuring this out on my own and not
having much luck.
Basically Text in A1 has USD or CDN or something else, if USD change color
of A1 to 5 and the neighbouring cells color changes to 5. (The text not the
background).

Function ColChange(r1 As Variant)

Select Case r1.Text
Case "USD"
r1.Font.ColorIndex = 5
r1.Offset(1, 2).Font.ColorIndex = 5
Case "CAD"
r1.Font.ColorIndex = 3
r1.Offset(1, 2).Font.ColorIndex = 3
Case Else
r1.Font.ColorIndex = xlNone
r1.Offset(1, 2).Font.ColorIndex = 5
End Select
End Function

 

[Guest]

I had the conditional formating doing this before, but you can't have excel
add based on color that way. Hmm.. What about using the USD or CDN then
doing the offset thing to add according to country? That should work and I
could still use the Conditional formating.. Hmm... The range varies each
time, I could still use the function CASE, do a loop, hmm.. Any pit falls I
might run into this this?

 

[Guest]

Please disregard, found the SUMIF function. Works like a charm.