VBA lookup a name based on a number

Hello everyone
Hope you can help since i'm new to VBA
I want to create a macro that has similarities to the Vlookup function.
The reason for this (illustrated in the Excel sheet) is that i have a list of different codes and the associated names (column F & G). In column B the user then inputs the code, and the macro should then automatically report the associated name in column C. If the user enters a code that doesn't have a name associated it should mark the column C cell as red (see example in sheet)

Thank you

Hello mrice
Tried to run your code and it worked almost perfectly. When i entered a number it showed me the associated name as supposed to. I then tried to press delete to clear the cell, but the name cell still remains red can this be changed somehow?
Furthermore, i forgot that cells containing the code and associated name (column 6 and 7) is located in sheet 2, but the input function is located in sheet 1. Can this be changed aswell?

Again thank you for the code! it's really appreciated