Type a code in a cell and have it replaced with a given name.

[Agnarr]

Hello everybody and you all do an amazing work.
I need your help please and i apologize in advance for any mistakes (English is not my native language).

I have an excel where I need to type a 4 digit code in a cell and when after pressing enter, I need it to be replaced by a name corresponding to said 4-digit code.
There is a separate sheet with the list of each code to a name.
For example:
Type in cell C4 "3625" and hit enter. Automatically it changes into "Mr. Addams".
I'm not even sure something like this is possible. I tried to to work my way around this but my vba knowledge is limited.

If the above is not possible then please help me solve this: Check each cell in a column and for each cell that is not blank have it copied to another cell in another column.
Example:
In column C cells 1,2,5, and 8 have values. Have these cells be copied in column z in the 1,2,4 and 8 position accordingly but for each cell that becomes blank, clear contents from the corresponding one as well. (if i delete values from c3 have the cell in z3 also become empty).

All of the above i would need them to work for any sheet i place the macro in, so no ties to worksheets names.

Thank you all in advance. Any help will be appreciated.

 

[bebo021999]

Assum sheet1, C1:C100 is input range
sheet names "code", A1:B3 is lookup range, with codes in column A, names in column B
Right click sheet1, view code, then paste below code into.

Option Explicit
'This code is placed in "sheet1" (input sheet) module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputR As Range, codeR As Range, cell As Range
Dim f
Set inputR = Range("C1:C100") ' range of input data. adjust to actual range
Set codeR = Worksheets("code").Range("A1:B3") ' assums sheet "code", range "A1:B3" contains lookup code. Adjust to actual sheet-range
If Intersect(Target, inputR) Is Nothing Then Exit Sub
With Application
    .EnableEvents = False
    For Each cell In Target
        Set f = codeR.Find(cell.Value, , , xlWhole)
        If Not f Is Nothing Then cell.Value = f.Offset(, 1).Value
    Next
    .EnableEvents = True
End With
End Sub

Book1
	A	B	C
1			1
2			2
3			3
4			Mr A
5			Mr B
6			3333
7			Mr B
8			Mr B
9			Mr A
10			Mr A
11			Mr C
12			Mr C
13			Mr C
14			Mr C
15			Mr C
16			Mr C
17
18
19
20
21
22
23
24
25
26
Sheet1

Book1
	A	B
1	1234	Mr A
2	5678	Mr B
3	3625	Mr C
code

 

[Agnarr]

Assum sheet1, C1:C100 is input range
sheet names "code", A1:B3 is lookup range, with codes in column A, names in column B
Right click sheet1, view code, then paste below code into.

Option Explicit
'This code is placed in "sheet1" (input sheet) module
Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputR As Range, codeR As Range, cell As Range
Dim f
Set inputR = Range("C1:C100") ' range of input data. adjust to actual range
Set codeR = Worksheets("code").Range("A1:B3") ' assums sheet "code", range "A1:B3" contains lookup code. Adjust to actual sheet-range
If Intersect(Target, inputR) Is Nothing Then Exit Sub
With Application
    .EnableEvents = False
    For Each cell In Target
        Set f = codeR.Find(cell.Value, , , xlWhole)
        If Not f Is Nothing Then cell.Value = f.Offset(, 1).Value
    Next
    .EnableEvents = True
End With
End Sub

Book1
	A	B	C
1			1
2			2
3			3
4			Mr A
5			Mr B
6			3333
7			Mr B
8			Mr B
9			Mr A
10			Mr A
11			Mr C
12			Mr C
13			Mr C
14			Mr C
15			Mr C
16			Mr C
17
18
19
20
21
22
23
24
25
26
Sheet1

Book1
	A	B
1	1234	Mr A
2	5678	Mr B
3	3625	Mr C
code

I can't believe it was that simple... I have been trying to do a whole other set of crap...
Thank you so much!!!!!!!

 

[Agnarr]

Now please one more thing... assume I want to do the same, but instead of the same cell, show the names to the cell next to the code.

 

[Agnarr]

I can't believe it was that simple... I have been trying to do a whole other set of crap...
Thank you so much!!!!!!!

one more question about this one please... in the same sheet i have a button that clears the whole list. now it's lagging incredibly. is there a workaround?