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 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
| Sub uson()
Dim xdfin As Long, xd As Long
Dim c As Range
Dim xdnam, xdusn, xdlau, xddel, xdtri, xdcha, xdhul, xdhco
Dim xdcg, xdmi, xdgl, xdon, xdcgp
Dim xda, xdb, xdc, xdd, xde, xdf, xdl, xdv
Dim xd0vi, xd0bl, xd1bl, xd0or, xd0rg, xd0vt, xd1vt, xd0ft, xd6bl
xda = "use"
xdb = "ON"
'xdfin = Sheets(xda).UsedRange.Rows(Sheets(xda).UsedRange.Rows.Count).Row
xd0vi = 10642560 'violet uscg
xd0bl = 12419407 'bleu miramar
xd1bl = 13020235 'bleu clair miramar
xd0or = 4626167 'orange miramar
xd0rg = 255 'rouge miramar OU rouge ERREUR
xd0vt = 32896 'vert great lakes
xd1vt = 307372 'vert pale great lakes
xd0ft = 3394611 'vert FAIT
xd6bl = 15773696 'bleu 1887
'Application.ScreenUpdating = False 'Inhibe la mise à jour affichage écran (diminue le temps d'exécution)
With Sheets(xda) 'Adapte à ta feuille
.AutoFilterMode = False 'Enlève le filtre auto s'il existe
xdfin = .Cells(Rows.Count, "AT").End(xlUp).Row 'Dernière ligne de données, on peut remplacer "AT" par la colonne contenant la dernière donnée
With .Range("AT1:AT" & xdfin)
.AutoFilter field:=1, Criteria1:=10 'On filtrer sur la colonne AT, remplace "X" du criteria1 par ton critère
For Each c In .SpecialCells(xlCellTypeVisible) 'Pour chaque cellule visible de la colonne AT
xd = c.Row 'Lig récupère les lignes visibles
If xd > 1 Then 'Pour ne pas traiter la première ligne des titres
'For xd = 2 To xdfin
Sheets(xda).Cells(xd, 46).Select
'xdc = Sheets(xda).Cells(xd, 46) 'AT verif
'If Not xdc = 10 Then GoTo xdsuit
xdd = Sheets(xda).Cells(xd, 46).Interior.color
If xdd = xd0ft Then GoTo xdsuit
xdon = Sheets(xda).Cells(xd, 43) 'AQ us nbr
xdtri = Sheets(xda).Cells(xd, 1) 'A tri
xdcha = Sheets(xda).Cells(xd, 2) 'B builder
xdhul = Sheets(xda).Cells(xd, 5) 'E hull#
xdhco = Sheets(xda).Cells(xd, 5).Interior.color
xdnam = Sheets(xda).Cells(xd, 7) 'G orig name
xdusn = Sheets(xda).Cells(xd, 14) 'N usn hull
xdlau = Sheets(xda).Cells(xd, 28) 'AB launch
'xdlau = Right(xdlau, 5)
xddel = Sheets(xda).Cells(xd, 29) 'AC deliv
'xddel = Right(xddel, 5)
xdcg = Sheets(xda).Cells(xd, 43).Interior.color 'AQ us on
xdmi = Sheets(xda).Cells(xd, 45).Interior.color 'AS miramar
xdgl = Sheets(xda).Cells(xd, 42).Interior.color 'AP ON
xdcgp = Sheets(xda).Cells(xd, 44).Interior.color 'AR imo
xdv = xd0ft
Set xdf = Sheets(xdb).Columns(2).Find(xdon, lookat:=xlWhole)
If xdf Is Nothing Then xdv = xd0rg: GoTo xdapr
xdl = xdf.Row
If Sheets(xdb).Cells(xdl, 3) = "VU" Then xdv = xd0rg: GoTo xdapr
Sheets(xdb).Cells(xdl, 3) = "VU"
Sheets(xdb).Cells(xdl, 4) = xdnam
Sheets(xdb).Cells(xdl, 5) = xdusn
Sheets(xdb).Cells(xdl, 6) = xdlau
Sheets(xdb).Cells(xdl, 7) = xddel
Sheets(xdb).Cells(xdl, 8) = xdtri
Sheets(xdb).Cells(xdl, 9) = xdcha
Sheets(xdb).Cells(xdl, 10) = xdhul
Sheets(xdb).Cells(xdl, 10).Interior.color = xdhco
If xdcgp = xd0vi Then Sheets(xdb).Cells(xdl, 11) = "Vu": xdv = xd0ft
If xdcg = xd0vi Then Sheets(xdb).Cells(xdl, 11) = "OK": xdv = xd0ft
If xdmi = xd0bl Then Sheets(xdb).Cells(xdl, 12) = "OK": xdv = xd0ft
If xdmi = xd1bl Then Sheets(xdb).Cells(xdl, 12) = "OK": xdv = xd0ft
If xdmi = xd0or Then Sheets(xdb).Cells(xdl, 12) = "Faire": xdv = xd0ft
If xdmi = xd0rg Then Sheets(xdb).Cells(xdl, 12) = "Vu": xdv = xd0ft
If xdgl = xd0vt Then Sheets(xdb).Cells(xdl, 13) = "OK": xdv = xd0ft
If xdgl = xd1vt Then Sheets(xdb).Cells(xdl, 13) = "OK": xdv = xd0ft
If xdgl = xd6bl Then Sheets(xdb).Cells(xdl, 15) = "vu": xdv = xd0ft
xdapr:
Sheets(xda).Cells(xd, 46).Interior.color = xdv
xdsuit:
'Next xd
End If
Next c
End With
.AutoFilterMode = False 'Enlève le filtre auto
End With
Worksheets(xda).Range("A1").AutoFilter
End Sub |
Partager