Private Sub CommandButton4_Click() 'Worksheet_Change(ByVal Target As Range)
Dim nm As Object, n As Integer
For Each nm In Application.Names
If nm.Name Like "diam*" Then 'recherche les noms commençant par diam
n = Replace(nm.Name, "diam", "")
With Range(nm.Name)
.Validation.InCellDropdown = Range("D35") = "non" And Range("puiss" & n) <> 0
If Range("D35") = "oui" Then .FormulaR1C1 = "=IF(puiss" & n & "=0,""pas de débit"",IF(R35C4=""non"",""rentrer un diametre"",IF(AND(R35C4=""oui"",R34C4=""cuivre""),INDEX(Tabl_Cu,MATCH(debit" & n & ",Q_Cu,1),1),IF(AND(R35C4=""oui"",R34C4=""acier""),INDEX(Tabl_Ac,MATCH(debit" & n & ",Q_Ac,1),1),IF(AND(R35C4=""oui"",R34C4=""PE""),INDEX(Tabl_PE,MATCH(debit" & n & ",Q_PE,1),1),"""")))))"
End With
End If
Next
End Sub