Automatically find and replace texts and numbers

hello.

I have an excel file with many users

i need certain texts to be automatically replaced once a user enters them.

Range should be anywhere in the worksheet

for example

B/RED into Brick Red

DARK GREEN into Dark Green

S/BLU into Sky Blue

C/GREY. into Charcoal Grey

and so on up to 10 or more

Can anyone help me in achieving what i need ?

Hi
right click on the sheet's tab, select 'view code', and copy the code below into your worksheet's code area

whenever you change a cell's contents on that sheet, the macro will look in that cell for the values in the first array ('ToRep'). If found, it will replace this with the corresponding value in the second array ('RepWith'). Note both are case sensitive and need to contain the same number of values.

Please Login or Register  to view this content.

Thanx, Nikky for your reply

i am getting

Compile Error: Ambiguous name detected: Worksheet_Change
in my current file.

That must be because i have another code there also for a different purpose

Please Login or Register  to view this content.

This problem I assume so because I created new file and your code works perfectly well in the new file.

But i need your code in my current file which is having another code for a different purpose.

any way to solve this problem ?

Please Login or Register  to view this content.

thanx Yudlagar,

Its working but with a slight annoyance.

i get error message while moving a row via a code

Run-time error '13':
Type mismatch

Although things get done but the message is annoying

my first code is for column K,
What is does is it automatically moves the whole row from the current unprotected sheet to another protected sheet once a date is entered in column K.

the code in the module1 is

Please Login or Register  to view this content.

Is there any way i can get rid of the error message ?

Hard to know if/how the two are related without access to the whole workbook. You could try disabling events and see if that helps:

Please Login or Register  to view this content.

that will stop the worksheet change macro from running.

can you please download the file and have a look ?

I think the problem is in your instr function. Instr has 3 arguments, the first one being start position, so presumably you want:

Please Login or Register  to view this content.

instead of

Please Login or Register  to view this content.

i dont see any difference in outcome after replacing ....did you ?