Sub Planif()
'supprimer protection
'ActiveSheet.Unprotect
'fin
Cells.Select
Cells.FormatConditions.Delete
C_ordre = 1
C_sequence = 2
C_operation = 3
C_article = 4
C_designation = 5
L_planif = 4
'nettoyage
L_nett = 4
L_deb = 4
Do Until Cells(L_nett, C_ordre).Value = ""
L_nett = L_nett + 1
Loop
Rows(L_deb & ":" & L_nett).Select
Selection.Delete
'nouvelle planification
L_Ref = 2
Do Until Sheets("Reference").Cells(L_Ref, 22).Value = ""
If Sheets("Reference").Cells(L_Ref, 22).Value = Cells(1, 2).Value Then
Cells(L_planif, C_ordre).Value = Sheets("Reference").Cells(L_Ref, 5).Value
Cells(L_planif, C_sequence).Value = Sheets("Reference").Cells(L_Ref, 6).Value
Cells(L_planif, C_operation).Value = Sheets("Reference").Cells(L_Ref, 7).Value
Cells(L_planif, C_article).Value = Sheets("Reference").Cells(L_Ref, 3).Value
Cells(L_planif, C_designation).Value = Sheets("Reference").Cells(L_Ref, 4).Value
ligne = 3
colonne = 6
Do Until Cells(ligne, colonne).Value = "" Or Cells(ligne, colonne).Value = Sheets("Reference").Cells(L_Ref, 25).Value
If Cells(ligne, colonne).Value = Sheets("Reference").Cells(L_Ref, 24).Value Then
Cells(L_planif, colonne).Value = "x"
End If
If Cells(L_planif, colonne - 1).Value = "x" Then
Cells(L_planif, colonne).Value = "x"
End If
colonne = colonne + 1
Loop
If Cells(ligne, colonne).Value = Sheets("Reference").Cells(L_Ref, 24).Value Or Cells(ligne, colonne).Value = Sheets("Reference").Cells(L_Ref, 25).Value Then
Cells(L_planif, colonne).Value = "x"
End If
L_planif = L_planif + 1
Else
End If
L_Ref = L_Ref + 1
Loop
Cells.Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""x"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.249946592608417
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
Cells.Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=SI(ET($A1<>$A2;A$3<>"""";$A1<>""Poste :"");VRAI;FAUX)"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Borders(xlBottom)
.LineStyle = xlContinuous
.TintAndShade = 0
.Weight = xlThin
End With
Selection.FormatConditions(1).StopIfTrue = False
'selection pour impression
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), Cells(L_planif - 1, 26)).Address
'protection
'ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
' , AllowFormattingCells:=True, AllowSorting:=True, AllowFiltering:=True
'fin
End Sub