Replace text in brackets with another cell's value

[Lowa]

Hi,

I have many cells in my worksheet that have text in brackets like this: "some text here [text in brackets to be changed] some additional text here".

I want to create a macro that replaces all text within brackets in my worksheet with the text value in cell C5.

Can someone please tell me how to do this?

[Richard Buttrey]

Hi,

One way - with your text in A1

Please Login or Register  to view this content.

[jindon]

vba

Please Login or Register  to view this content.

[Lowa]

Thanks for your replies!

I tried jindon's vba and it worked

Just one issue: when the macro runs it changes the formatting of the text and brackets.

Ideally I would like to keep the text within the brackets in italics and keep the font colour of the brackets themselves white.

Is there any way to do this e.g. through using a new macro or editing the current one?

[jindon]

Try

Please Login or Register  to view this content.

[Lowa]

This worked for most of them, however I found some cells that begin with a bracket like this : "[text in brackets] additional text."

The font of all of the text in these cells was turned white, instead of just the brackets.

Any way to fix this?

[jindon]

Can't replicate it here, so upload a workbook...

[Lowa]

sample excel file.xlsm

Please see attached workbook

The error only occurs if you type something in cell C5 more than once.

[jindon]

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[Lowa]

I put it in but I still got the same error.

I have a vba code that runs the macro every time cell C5 is changed if that is any help.

Would it work if I had a seperate macro to change the text in the brackets, a seperate macro to change the brackets to white, and a seperate macro to change the text to italics?

[jindon]

Try this one

Please Login or Register  to view this content.

Due to the font color is determined by the first character of the cell, when Replace method runs, it apply to the cell color to replaced cell.

[Lowa]

Thank you! this worked perfectly

I will add to your reputation and mark the thread as solved

[jindon]

You are welcome and thanks for the rep.