Sub Vider_le_SURPLUS()
' premier code
Application.Calculation = xlCalculationManual
derligne = Cells(Rows.Count, 3).End(3).Row
Range("N13:N" & derligne).ClearContents
For I = 13 To derligne
If (Cells(I, 11) Like "*DERNIER*" And Cells(I, 11).Interior.ColorIndex = 35) Then
derligneZ = Cells(I, 11).End(4).Row
Range(Cells(I, 11), Cells(derligneZ, 11)).Copy
Cells(I - 1, 14).PasteSpecial Paste:=xlValues
I = derligneZ
Else
Cells(I, 11).Copy Destination:=Cells(I, 14) ' si copie des valeurs et des formats
'Cells(I, 14).Paste
'Cells(I, 14).PasteSpecial Paste:=xlValues 'si copie uniquement des valeurs
End If
Next I
For I = 13 To derligne
If Cells(I, 14) Like "*DERNIER*" Then
With Cells(I, 14)
.Interior.ColorIndex = 46
.Font.ColorIndex = 2
End With
With Cells(I + 1, 14)
.Interior.ColorIndex = 35
.Font.ColorIndex = 0
End With
End If
Next I
Application.Calculation = xlCalculationAutomatic
Cells(8, 15).Select
End Sub