Format Text During Function

[PJohnson]

I have a function that transposes names.

Let's say for example that it is:

Smith Jr., James

It works fine but I would like to format (Make Bold and REd) the text in the
resultant cell if it meets a particular condition. I cannot figure out why
this does not work. The function works fine and returns the correct results
but does not format the text if the condition is met.

Any help appreciated...

- - - - - - - - - -- - - - - -
Public Function TransName(MyText) As String

Dim LName, FName As String
Dim Comma, Legnth, JrSuff As Byte

Length = Len(MyText)
Comma = InStr(1, MyText, ",")
Length = Length - Comma

JrSuff = InStr(1, MyText, "Jr")
'Debug.Print JrSuff

LName = Left(MyText, Comma - 1)
FName = Right(MyText, Length)

TransName = FName & " " & LName
TransName = Trim(TransName)

'==== THE FOLLOWING DOES NOT WORK=====

If JrSuff >= 1 Then
Selection.Font.ColorIndex = 3
Selection.Font.Bold = True
End If
'========================================
End Function

 

[Bernie Deitrick]

PJohnson,

"=== THE FOLLOWING DOES NOT WORK====="

because a function can only return a value to the calling cell, and cannot
otherwise change the Excel environment (such as font color or style).

You would need to use a macro or worksheet event to achieve your results.

HTH,
Bernie
MS Excel MVP

 

[Ron Rosenfeld]

I have a function that transposes names.

Let's say for example that it is:

Smith Jr., James

It works fine but I would like to format (Make Bold and REd) the text in the
resultant cell if it meets a particular condition. I cannot figure out why
this does not work. The function works fine and returns the correct results
but does not format the text if the condition is met.

Any help appreciated...

A function can ONLY return a value. It cannot alter properties. (nor can it
call a SUB to do that alteration, a far as I know).

Perhaps you could use conditional formatting to accomplish your goals.


--ron