Why do we lose text formatting when we replace part of string, and how to avoid this

[ThrottleWorks]

Hi,
I am replacing part of string from a text value.
When I replace string, I lose text formatting. How do I avoid this.

For example,
Input - > You know what, car driving is awesome (awesome word is in red font and bold type).

When I replace ‘car driving’ with ‘bike riding’ I lose text formatting.
Output - > You know what, bike riding is awesome.

I am using below mentioned code to replace string.

Range("B7:B9").Select
Selection.Replace What:="Jul", Replacement:=MyMonthName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

I want to preserve formatting. Can anyone please help in this.

 

[Chihiro]

I believe that is default behaviour for Excel.

Work around is to re-apply formatting after replace.

Edit: Found link below. The code in post #5 may help you.
http://www.mrexcel.com/forum/excel-...replace-loses-text-properties-formatting.html

 

[Hui]

Have a read of:
http://www.mrexcel.com/forum/excel-...replace-loses-text-properties-formatting.html

 

[ThrottleWorks]

Hi @Chihiro sir thanks a lot for the help.
Got help from @Deepak sir's post mentioned below.
I was not aware that excel behave in such a way.

Have a nice weekend.

http://forum.chandoo.org/threads/fo...ll-with-specific-font-type.28885/#post-172850

Hi @Hui sir, thanks a lot. I am going through this. Have a nice weekend.

 

[Hui]

Handling Text really isn't Excel's strong point

 

[ThrottleWorks]

Sir, to be honest, I never anticipated that excel will behave in such way.

My perception was, I am writing wrong lines, and correct (replace) code will resolve my issue. :D