Macro to replace cells value automatically based on another cell change event

[phsilverhp]

I know there are several threads but I wasn't able to find something which will fit my needs.
I have a sheet in which I want to Find & Replace cell values based on the cell $B1 which will be a drop down list. Also I need to mention that the cells which I want to replace the string have formulas. Here's an example of what a cell looks:
=IF('http://sharepoint.www.com/Industrial Engineering/2011.06 June/[Workbook.xls]June'!I218=0,"",'http://sharepoint.www.com/Industrial Engineering/2011.06 June/[Workbook.xls]June'!I218)

By changing the value from $B1 from Workbook to Workbook1 I want to automatically replace in a specific range the values from the cell: Workbook.xls with Workbook1.xls

I have a code which will work but I need to narrow down the replace function to a specific range like $A2:$BA250

My macro looks like this:

Please Login or Register  to view this content.

[mambofats]

Hi,

you can include :

Please Login or Register  to view this content.

I didn't test this, but i think this is the way to go.

Alain

[TMS]

Please add code tags to your OP, as in the following code example.

I think you might be better with this, also untested

Please Login or Register  to view this content.

Regards

[phsilverhp]

10x both for such a quick reply, I've tried both codes but TMShucks's works like a charm.
Thanks a lot.

[TMS]

You're welcome. Thanks for the feedback and rep.

Please go back to your original thread and add Code Tags. According to the forum rules, we should not really have provided an answer unless you comply with the rules. The Moderators are quite strict about applying the forum rules and we could all be reprimanded for not following them. It's a small price to pay to make this a useful repository of knowledge.

Please also mark your thread as Solved. See my signature for details or the FAQ.

Regards