j'ai trouvé (en cherchant un peu sur le forum)
Private Sub Worksheet_Change(ByVal Target As Range)
'Macro Dan & Nad - xl pratique le 03/12/08
If Target.Count > 1 Then Exit Sub
Select Case Target
Case Is = "ABDOU"
Target.Interior.ColorIndex = 53
Target.Offset(-1, 0).Interior.ColorIndex = 53
Case Is = "ALAIN"
Target.Interior.ColorIndex = 35
Target.Offset(-1, 0).Interior.ColorIndex = 35
Case Is = "CINDY"
Target.Interior.ColorIndex = 10
Target.Offset(-1, 0).Interior.ColorIndex = 10
Case Is = "DIANA"
Target.Interior.ColorIndex = 42
Target.Offset(-1, 0).Interior.ColorIndex = 42
Case Is = "EMILIE"
Target.Interior.ColorIndex = 46
Target.Offset(-1, 0).Interior.ColorIndex = 46
Case Is = "FRANCO"
Target.Interior.ColorIndex = 38
Target.Offset(-1, 0).Interior.ColorIndex = 38
Case Is = "GAELLE"
Target.Interior.ColorIndex = 40
Target.Offset(-1, 0).Interior.ColorIndex = 40
Case Is = "GUILLAUME"
Target.Interior.ColorIndex = 27
Target.Offset(-1, 0).Interior.ColorIndex = 27
Case Is = "GUYLAINE"
Target.Interior.ColorIndex = 14
Target.Offset(-1, 0).Interior.ColorIndex = 14
Case Is = "HERMANN"
Target.Interior.ColorIndex = 36
Target.Offset(-1, 0).Interior.ColorIndex = 36
Case Is = "MAGOMA"
Target.Interior.ColorIndex = 41
Target.Offset(-1, 0).Interior.ColorIndex = 41
Case Is = "MORGANE"
Target.Interior.ColorIndex = 54
Target.Offset(-1, 0).Interior.ColorIndex = 54
Case Is = "PASCAL"
Target.Interior.ColorIndex = 39
Target.Offset(-1, 0).Interior.ColorIndex = 39
Case Is = "PIERRE-L"
Target.Interior.ColorIndex = 45
Target.Offset(-1, 0).Interior.ColorIndex = 45
Case Is = "ROMAIN"
Target.Interior.ColorIndex = 28
Target.Offset(-1, 0).Interior.ColorIndex = 28
Case Is = "RENE"
Target.Interior.ColorIndex = 20
Target.Offset(-1, 0).Interior.ColorIndex = 20
Case Is = "SEBASTIEN"
Target.Interior.ColorIndex = 16
Target.Offset(-1, 0).Interior.ColorIndex = 16
Case Is = "STEPHANIE"
Target.Interior.ColorIndex = 7
Target.Offset(-1, 0).Interior.ColorIndex = 7
Case Else
Target.Interior.ColorIndex = -4142
End Select
End Sub