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

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
----------------------------------
"Ross Withey" wrote in message
...
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

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

--
Regards,
Tom Ogilvy

Ross Withey wrote in message
...
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

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!

"Ross Withey" wrote in message
...
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

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

--
regards,
Tom Ogilvy

Ross Withey wrote in message
...
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!

"Ross Withey" wrote in message
...
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