1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
|
Dim passe As Byte ' avant la sub pour être reconnue dans toutes les proédures
Sub worksheet_change(ByVal target As Range)
Dim result As Range
Dim Prelig1 As Long, Derlig1 As Long
Dim col2 As String
If passe = 1 Then Exit Sub
Set DebBD1 = Range("B149")
Prelig1 = DebBD1.Row
Derlig1 = DebBD1.End(xlDown).Row
Set DebBD2 = Range("B215")
Prelig2 = DebBD2.Row
Derlig2 = DebBD2.End(xlDown).Row
If Not Intersect(Range("B12:B" & Prelig1 - 4), target) Is Nothing _
Or Not Intersect(Range("N12:N" & Prelig1 - 4), target) Is Nothing _
Or Not Intersect(Range("Z12:Z" & Prelig1 - 4), target) Is Nothing _
Or Not Intersect(Range("C12:C" & Prelig1 - 4), target) Is Nothing _
Or Not Intersect(Range("O12:O" & Prelig1 - 4), target) Is Nothing _
Or Not Intersect(Range("AA12:AA" & Prelig1 - 4), target) Is Nothing Then
col = target.Column
ActiveSheet.Unprotect
'Application.EnableEvents = FalseIf passe = 1 Then Exit Sub
If (target.Column = 3 Or target.Column = 15 Or target.Column = 27) Then
If target.Value <> "" Then
Set result = Range(Cells(Prelig2, col - 1), Cells(Derlig2, col - 1)).Find(What:=target.Value, LookIn:=xlValues)
Range(Cells(target.Row, col + 1), Cells(target.Row, col + 5)).Value = Range(Cells(result.Row, col + 1), Cells(result.Row, col + 5)).Value
Else
Set result = Range(Cells(Prelig1, col - 1), Cells(Derlig1, col - 1)).Find(What:=target.Offset(0, -1).Value, LookIn:=xlValues)
Range(Cells(target.Row, col + 2), Cells(target.Row, col + 6)).Value = Range(Cells(result.Row, col + 2), Cells(result.Row, col + 6)).Value
End If
End If
If (target.Column = 2 Or target.Column = 14 Or target.Column = 26) Then
If target.Offset(0, 1) = "" Then
Set result = Range(Cells(Prelig1, col), Cells(Derlig1, col)).Find(What:=target.Value, LookIn:=xlValues)
Range(Cells(target.Row, col + 2), Cells(target.Row, col + 6)).Value = Range(Cells(result.Row, col + 2), Cells(result.Row, col + 6)).Value
Else
Set result = Range(Cells(Prelig2, col), Cells(Derlig2, col)).Find(What:=target.Offset(0, 1).Value, LookIn:=xlValues)
Range(Cells(target.Row, col + 2), Cells(target.Row, col + 6)).Value = Range(Cells(result.Row, col + 2), Cells(result.Row, col + 6)).Value
End If
End If
'Application.EnableEvents = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub |
Partager