Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Fin:
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub
If Target.Column = 10 Then
Traitement_Colonne_J (Target) ' Execution votre macro car colonne J
ElseIf Target.Column = 5 Then
Traitement_Colonne_E (Target) ' Execution ma macro car colonne E
End If
Fin:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Sub Traitement_Colonne_J(Target)
Lignee = Target.Row
If Range("I" & Lignee).Value <> "" Then
Dim i As Integer
If Lignee >= 10 Then
If Range("I" & Lignee) = "Préventif" Then
On Error Resume Next
With Range("K" & Lignee).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=indirect(J" & Lignee & ")"
'.IgnoreBlank = True
.InCellDropdown = True
End With
ElseIf Range("I" & Lignee) <> "Préventif" Then
With Range("K" & Lignee).Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
End If
End If
End If
End If
End Sub
Sub Traitement_Colonne_E(Target)
Dim i%, j%
For i = 62 To 92
For j = 3 To 25
If Feuil1.Range("E" & i) = Feuil4.Range("D" & j) Then
Feuil1.Range("F" & i) = Feuil4.Range("E" & j)
End If
Next j
Next i
End Sub