Liste déroulante avec conservation de la couleur

Private Sub Worksheet_Change(ByVal Target As Range)
Set Target = Intersect(Target, Range("B2:K8"))
If Target Is Nothing Then Exit Sub
Dim ref As Range
Set ref = Range("A12:A21").Find(Target.Cells(1, 1), LookIn:=xlFormulas)
If ref Is Nothing Then Target.Interior.ColorIndex = xlNone: Exit Sub
Target.Interior.ColorIndex = ref.Interior.ColorIndex
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect([planning], Target) Is Nothing Then
    On Error Resume Next
    Target.Interior.ColorIndex = [couleurs].Find(Target, LookAt:=xlWhole).Interior.ColorIndex
  End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
If Intersect(Source, Sh.Range("B2:K8")) Is Nothing Or Source.Count > 1 Then Exit Sub
Dim ref As Range
Set ref = Sh.Range("A12:A21").Find(Source, LookIn:=xlFormulas)
If ref Is Nothing Then Source.Interior.ColorIndex = xlNone: Exit Sub
Source.Interior.ColorIndex = ref.Interior.ColorIndex
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
If [COLOR="Red"]Sh.Index < 2 Or[/COLOR] Intersect(Source, Sh.Range("B2:K8")) Is Nothing Or Source.Count > 1 Then Exit Sub
Dim ref As Range
Set ref = [COLOR="Red"][test].[/COLOR]Find(Source, LookIn:=xlFormulas)
If ref Is Nothing Then Source.Interior.ColorIndex = xlNone: Exit Sub
Source.Interior.ColorIndex = ref.Interior.ColorIndex
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range)
Set Source = Intersect(Source, Sh.Range("B2:K8"))
If Source Is Nothing Then Exit Sub
If Application.CountA(Source) = 0 Then Source.Interior.ColorIndex = xlNone: Exit Sub 'effacement d'une plage
If Source.Count > 1 Then Exit Sub
Dim ref As Range
Set ref = [test].Find(Source, LookIn:=xlFormulas)
If ref Is Nothing Then Source.Interior.ColorIndex = xlNone: Exit Sub
Source.Interior.ColorIndex = ref.Interior.ColorIndex
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Source As Range