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
| Sub Planning()
Dim semaine As Integer
Dim nom As String
Dim H, V As Long
Workbooks.Open Filename:= _
"M:\CQ\Techniciens\Stagiaire\suivi validité appareillages.xlsm"
With Workbooks("suivi validité appareillages").Sheets(1)
For i = 2 To 300
If .Cells(i, 10) = "x" Then
Select Case True
Case .Cells(i, 6) = "trimestrielle" Or .Cells(i, 6) = "4 mois"
semaine = .Cells(i, 5)
nom = .Cells(i, 1)
For m = 3 To 56
If Workbooks("Planning Matériel Labo 2018").Sheets(2).Cells(2, i) = semaine Then
H = m
End If
If Workbooks("Planning Matériel Labo 2018").Sheets(2).Cells(i, 2) = nom Then
V = m
End If
Range(Cells(H + 1, V), Cells(H - 1, V)).Interior.ColorIndex = 10
Next m
'Case .Cells(6, i) = "annuelle" Or .Cells(6, i) = "semestrielle" Or .Cells(6, i) = "2 ans"
End Select
End If
Next i
End With
Workbooks("suivi validité appareillages.xlsm").Close
End Sub |
Partager