Change Format of Only Some Text in Cell

[roadkill]

The user interface for Excel allows you to select letters or words within a
string of text in a cell and change the format (bold, color, etc.) for just
the selected letters or words. I haven't been able to figure out how to do
this same selective formatting programmatically. Does VBA provide this
capability and, if so, how?

Thanks,
Will

 

[John Huschka]

Look for the "Characters" collection, which presents the text of a cell as a
collection of characters.

 

[Gary''s Student]

from the recorder:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/19/2007
'

'
ActiveCell.FormulaR1C1 = "hello world"
With ActiveCell.Characters(Start:=1, Length:=1).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=2, Length:=3).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=5, Length:=7).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("D9").Select
End Sub