Format Text During Function

  • Thread starter Thread starter PJohnson
  • Start date Start date
P

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
 
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
 
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...
Click to expand...

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
 
Back
Top