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, n As Integer
Application.EnableEvents = False
For Each nm In Application.Names
If nm.Name Like "diam*" Then 'recherche les noms commençant par diam
[COLOR="Red"]n = Replace(nm.Name, "diam", "")[/COLOR]
With Range([COLOR="Red"]nm.Name[/COLOR])
.Validation.InCellDropdown = Range("D35") = "non" And [COLOR="Red"]Range("puiss" & n)[/COLOR] <> 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),""""))))"
End With
End If
Next
Application.EnableEvents = True
End Sub