I assume you want to use this function in a worksheet. You probably have it
in a sheet module. That will cause a #Name error - it should be in a general
module

all that said,
You should use conditional formatting to do this.

A UDF used in a worksheet can only return a value to a cell - it can't put
values in other cells or change colors in any cell or otherwise alter the
Excel environment.

--
Regards,
Tom Ogilvy



"Mitch" wrote:

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