Hey everyone and many thanks in advance!!

I am creating a monthly roster for work. In one sheet I have the monthly
roster with all the employees down the side and the days across the top. In
the middle I have made it so that when a two letter code is entered into that
cell it does the following:
1. A macro I found on the discussion group changes the colour of the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Num As Long
Dim rng As Range
Dim vRngInput As Variant
Set vRngInput = Intersect(Target, Range("tasks"))
If vRngInput Is Nothing Then Exit Sub
For Each rng In vRngInput
'Determine the color
Select Case rng.Value
Case Is = "nr": Num = 6
Case Is = "nw": Num = 6
Case Is = "rd": Num = 6
Case Is = "al": Num = 6
Case Is = "lw": Num = 6
Case Is = "tr": Num = 6
Case Is = "wc": Num = 6
Case Is = "ph": Num = 6
Case Is = "ll": Num = 6
Case Is = "ol": Num = 6
Case Is = "pa": Num = 15
Case Is = "ra": Num = 16
Case Is = "pc": Num = 13
Case Is = "cf": Num = 14
Case Is = "cr": Num = 3
Case Is = "ci": Num = 33
Case Is = "rc": Num = 35
Case Is = "cb": Num = 46
Case Is = "fp": Num = 44
Case Is = "fr": Num = 11
Case Is = "cw": Num = 38
Case Is = "fw": Num = 39
Case Is = "lc": Num = 41
Case Is = "ff": Num = 34
Case Is = "ft": Num = 25
End Select
'Apply the color
rng.Interior.ColorIndex = Num
Next rng
End Sub

2. A lookup function returns the full task name into that days roster in a
separate sheet, next to the employees name.

=LOOKUP('Monthly Team Roster'!B6,'Monthly Team Roster'!$AL$6:$AL$31,'Monthly
Team Roster'!$AM$6:$AM$31)

-----------------------

Now what I want to do is when I say enter ci into B6 in the sheet Monthly
Team Roster it will update C6 in sheet A Monday to Inventory and change the
cell colour to a light blue (33).

Is it possible to have a macro to do this? As work only has excel 2000 and
it is limited to 3 conditional formats and I can't install the add on for 30
conditional formats because I'm not allowed to and it would then have to be
installed on all the computers at work.

I have scoured these discussion groups for many hours but could not find
anything.

Thanks again 1000 times!!!