two worksheets working togeather

Is there a way to write a macro to have a cell be colored if a name is typed in based off a range of cells on another sheet. Also this range needs to be dynamic because people can be added or deleted at any time.

So based on the file I attached if I enter Alex on the calender worksheet this name should appear yellow because he is in the "IP" column but if I move him to say the "MB" column he should be colored green.

I don't want to use conditional rules because I have a lot of names and they can change columns all the time.