Find Replace loses text properties/formatting
 

Hello there,

My problem is as follows:

I currently have a cell that is a merged cell, so it spans about 20 rows, (however when you click on it, it always shows one cell, ie, J14) and in the cell there is text which is bold,

italic, underlined and normal.

I wish to replace a word that is in bold in the cell, (ie changing N to Z), however, when using the find and replace it makes the whole cell bold. I guess what is the CODE, as i wish to

code it, (or if worst comes to worst record a macro) for the find and replace to preserve the cell text properties/formatting.

I thought Excel might have been smart enough to realise if text was bold then when you replace it, the text you replaces becomes bold too. Or if text was underlined and you replaced it,

then the new text was underlined. Yeah?

Currently my code works to replace the text but changes the formatting. Can anyone expand on it to help with code to preserve the contents.

Sub findReplaceText()

For Each mycell In Range("J4:J4")

mycell.Replace What:="N", Replacement:="Z"

Next

End Sub


Thanks in advance

When you open the replace dialog box there is an options button in the lower right corner.

Check to make sure there is not special formatting to the "replace" field.