Format part of text string, not entire cell (VBA)

[Craig__]

Below is a macro with an InputBox for users to add a reference number beneath existing names in a column. For example, a user might select a cell with "John Smith & Co." in it, and the result might be:

John Smith & Co
Code Number:
hwu 453290

Is there a way to change the inputted text (hwu 453290) on line 3 to blue, and also change the inputted text to uppercase if not already in uppercase.

Additional notes:
Line 1 and 2 must remain in black text.
I need all 3 lines to be in the same cell rather than on 3 separate rows.
The reference numbers are not of a constant length, they can be anywhere between 7-14 characters.

Thanks guys.


Sub Format_Text_From_InputBox()
Dim strName As String
strName = InputBox(Prompt:="Please type your reference number" _
& vbCr & "in the box below, then click OK.", _
Title:="", Default:="")
If strName = " " Or _
strName = vbNullString Then
Exit Sub
Else
ActiveCell.Value = ActiveCell.Value & Chr(10) _
& "Code Number:" & Chr(10) & strName
End If
End Sub

 

[VoG]

You need to look at Instr and the Format property,

 

[Rick Rothstein]

Give this modification to your code a try...

Sub Format_Text_From_InputBox()
  Dim strName As String, LastLineFeed As Long
  strName = InputBox(Prompt:="Please type your reference number" & vbLf & "in the box below, then click OK.", Title:="", Default:="")
  If Trim(strName) = "" Then Exit Sub
  ActiveCell.Value = ActiveCell.Value & vbLf & "Code Number:" & vbLf & strName
  LastLineFeed = InStrRev(ActiveCell.Value, vbLf)
  ActiveCell.Characters(LastLineFeed + 1).Font.ColorIndex = 5
End Sub

 

[Craig__]

Many thanks Vog and Rick for your help. Rick this is just what I needed, it works great. Cheers.