Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Interior.ColorIndex <> 34 Then Exit Sub 'si la cellule n'est pas bleue
Dim nm As Object
For Each nm In Application.Names
If nm.Name Like "diam*" Then 'recherche les noms commençant par diam
With Range(Replace(nm, "=", ""))
.Validation.InCellDropdown = Range("B7") = "non" And .Offset(1) <> 0
If Range("B7") = "oui" Then .FormulaR1C1 = "=IF(R[1]C=0,""pas de débit"",(IF(R7C2=""non"",""rentrer un diametre"",IF(AND(R7C2=""oui"",R6C2=""cuivre""),INDEX(Données!R4C8:R17C10,MATCH(R[2]C,Données!R4C10:R17C10,1),1),IF(AND(R7C2=""oui"",R6C2=""acier""),INDEX(Données!R4C11:R14C13,MATCH(R[2]C,Données!R4C13:R14C13,1),1),"""")))))"
End With
End If
Next
End Sub