Option Compare Text
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("D1,E1,F1,G1"))
   If vRngInput Is Nothing Then Exit Sub
       On Error GoTo endit
   Application.EnableEvents = False
   For Each rng In vRngInput
   'Determine the color
       Select Case rng.Value
       Case Is = "Able": Num = 10    'green
       Case Is = "Baker": Num = 1    'black
       Case Is = "Charie": Num = 5    'blue
       Case Is = "Doofus": Num = 7    'magenta
       Case Is = "Edgar": Num = 46    'orange
       Case Is = "Francis": Num = 3       'red
       End Select
   'Apply the color
   rng.Interior.ColorIndex = Num
   Next rng
endit:
       Application.EnableEvents = True
End Sub

This is sheet event code.  Right-click on the sheet tab and "View Code".

Copy/paste the code into that sheet module.

Adjust range, names and colors to suit.

Gord Dibben  MS Excel MVP