Change the Formatting of All Instances of a word in a worksheet in Excel
 

Is there a way to change the formatting (make it bold or change the fon
color) of all the instances of a particular word (not the whole cell) i
excel ? I am able to do it in word but would like to use the macro i
excel & power point also. Any help would be appreciated.

Thanks,
Jacki

--
jaikal2
-----------------------------------------------------------------------
jaikal24's Profile: http://www.excelforum.com/member.php...fo&userid=3521
View this thread: http://www.excelforum.com/showthread.php?threadid=54978

Change the Formatting of All Instances of a word in a worksheet in Excel
 

Lets put it this way: Can you format a single word in a cell in Excel
or any spreadsheet
on the market? Word and Power Point are set up for character by
character formatting.
Nothing I have seen in a spreadsheet (although I don't use Excel much)
remotely suggests this is possible.

jaikal24 wrote:
Is there a way to change the formatting (make it bold or change the font
color) of all the instances of a particular word (not the whole cell) in
excel ? I am able to do it in word but would like to use the macro in
excel & power point also. Any help would be appreciated.

Thanks,
Jackie


--
jaikal24
------------------------------------------------------------------------
jaikal24's Profile: http://www.excelforum.com/member.php...o&userid=35213
View this thread: http://www.excelforum.com/showthread...hreadid=549781

Change the Formatting of All Instances of a word in a workshee
 

Yes you can format substrings in a cell: press F2, highlight the target
substring apply the format & press <Enter

"mike1942f" wrote:

Lets put it this way: Can you format a single word in a cell in Excel
or any spreadsheet
on the market? Word and Power Point are set up for character by
character formatting.
Nothing I have seen in a spreadsheet (although I don't use Excel much)
remotely suggests this is possible.

jaikal24 wrote:
Is there a way to change the formatting (make it bold or change the font
color) of all the instances of a particular word (not the whole cell) in
excel ? I am able to do it in word but would like to use the macro in
excel & power point also. Any help would be appreciated.

Thanks,
Jackie


--
jaikal24
------------------------------------------------------------------------
jaikal24's Profile: http://www.excelforum.com/member.php...o&userid=35213
View this thread: http://www.excelforum.com/showthread...hreadid=549781

Change the Formatting of All Instances of a word in a worksheet in
 

Hi Jackie,

you can format substrings in cells as suggested above. Your first problen is
to search for the word in the cells, then find its position in the string.
Once you have done this you can apply your formating. Below is an example
from the macro recorder where I applied different formats to part of a
string.

Range("F1").Select
ActiveCell.FormulaR1C1 = "MyClasses.Add item := Inst, key := CStr(Num)"
With ActiveCell.Characters(Start:=1, Length:=2).Font
.Name = "Arial Unicode MS"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=3, Length:=7).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=10, Length:=1).Font
.Name = "Arial Unicode MS"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=11, Length:=3).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=14, Length:=1).Font
.Name = "Arial Unicode MS"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=15, Length:=8).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=23, Length:=6).Font
.Name = "Arial Unicode MS"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=29, Length:=6).Font
.Name = "Arial Unicode MS"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With ActiveCell.Characters(Start:=35, Length:=10).Font
.Name = "Arial Unicode MS"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub

--
Tony Green


"jaikal24" wrote:


Is there a way to change the formatting (make it bold or change the font
color) of all the instances of a particular word (not the whole cell) in
excel ? I am able to do it in word but would like to use the macro in
excel & power point also. Any help would be appreciated.

Thanks,
Jackie


--
jaikal24
------------------------------------------------------------------------
jaikal24's Profile: http://www.excelforum.com/member.php...o&userid=35213
View this thread: http://www.excelforum.com/showthread...hreadid=549781