Macro : insérer formule selon couleur de fond

Sub Formules()
'Feuil1 et Feuil2 sont les CodeName des feuilles
Dim coul As Range, plage As Range, p As Range, c As Range
Set coul = Feuil1.[A1:A3]
Set plage = Intersect(Feuil2.[1:1], Feuil2.UsedRange)
For Each p In plage
  For Each c In coul
    If p.Interior.Color = c.Interior.Color Then
      p(5) = c(1, 2)
      Exit For
    End If
  Next
Next
End Sub

Sub Formules()
'Feuil1 et Feuil2 sont les CodeName des feuilles
Dim coul As Range, plage As Range, p As Range, c As Range
Set coul = Feuil1.[A1:A3]
Set plage = Intersect(Feuil2.[1:1], Feuil2.UsedRange)
For Each p In plage
  For Each c In coul
    If p.Interior.Color = c.Interior.Color Then
      p(5).FormulaR1C1 = c(1, 2).Value
      Exit For
    End If
  Next
Next
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Feuil1 est le CodeName de la feuille
Dim coul As Range, plage As Range, p As Range, c As Range
Set coul = Feuil1.[A1:A3]
Set plage = Intersect([1:1], Me.UsedRange)
Application.EnableEvents = False 'désactive les événements
For Each p In plage
  For Each c In coul
    If p.Interior.Color = c.Interior.Color Then
      p(5).FormulaR1C1 = c(1, 2).Value
      Exit For
    End If
  Next
Next
Application.EnableEvents = True
End Sub