Changing font of part of text in a cell

[Ross Withey]

Hello,
Using VBA, I am trying to use Instr and Range(x).characters to change part
of a cell's text in Excel 97.
I do a loop to find a cell with the same text as a control cell, but as the
text may be at different points in a cell, (e.g. one might say "Try this"
and the other might say "try again". How can I get Excel to find the "try"
in cell A2 and embolden the "try" but not the rest if it matches the "try"
in another cell (found in a loop)? It's got me beat!
Thanks if you can help!
Ross

 

[Patrick Molloy]

Sub test()
SetBoldON Range("E7"), 4, 3
End Sub

Sub SetBoldON(ByRef Target As Range, lStart As Long, lLength As Long)
With Target.Cells.Characters(start:=lStart, length:=lLength).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

End Sub


Patrick Molloy
Microsoft Excel MVP

 

[Tom Ogilvy]

Sub AAAA()
For i = 1 To 100
Set rng = Cells(i, "A")
If InStr(1,rng, Range("B9"),vbTextCompare) > 0 Then
iloc = InStr(1,rng, Range("B9"),vbTextCompare)
rng.Characters(iloc, Len(Range("B9"))).Font.Bold = True
End If
Next

End Sub

Tested in xl97, SR2

 

[Ross Withey]

Thanks for your help, Tom and Patrick. One more thing..!
Having changed part of the cell text, is it possible to copy it elsewhere?
Range("A1").characters only seems to support the .font property and does not
allow copying. Do you know of a workaround?
Thanks!

 

[Tom Ogilvy]

You can copy the cell - copying anything else will lose the rich text
formatting (although you could reapply it.)