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
| Private Sub CommandButton3_Click()
'Evite de voir les opérations intermédiaire sur les fichiers
Application.ScreenUpdating = False
'déclarations des variables
Dim i As Long
Dim j As Long
Dim Sh As Worksheet
Dim Tablo1
Dim Tablo2
Workbooks.Open ("D:\Couz\test1.xlsx")
Set Sh = Workbooks("test1.xlsx").Worksheets("Feuil1") 'feuille
With Sh
Tablo1 = .Range(.Cells(2, 1), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Value
End With
Workbooks("test1.xlsx").Close False
Workbooks.Open ("D:\Couz\test2.xlsx")
Set Sh = Workbooks("test2.xlsx").Worksheets("Feuil1")
With Sh
Tablo2 = .Range(.Cells(4, 1), .Cells(.UsedRange.Rows.Count, .UsedRange.Columns.Count)).Value
End With
For i = LBound(Tablo2, 1) To UBound(Tablo2, 1)
For j = LBound(Tablo1, 1) To UBound(Tablo1, 1)
If Tablo2(i, 1) = Tablo1(j, 2) Then
If ((Tablo2(i, 1) & Tablo2(i, 4) & Tablo2(i, 5) = Tablo1(j, 2) & Tablo1(j, 4) & Tablo1(j, 5)) Or (Tablo2(i, 1) & Tablo2(i, 8) & Tablo2(i, 5) = Tablo1(j, 2) & Tablo1(j, 4) & Tablo1(j, 5)) Or (Tablo2(i, 1) & Tablo2(i, 9) & Tablo2(i, 5) = Tablo1(j, 2) & Tablo1(j, 4) & Tablo1(j, 5))) Then
Sh.Cells(i + 3, 1).Interior.Color = 16777215
Else
Sh.Cells(i + 3, 1).Interior.Color = 2
Exit For
End If
End If
Next j
Next i
Set Sh = Nothing
Application.ScreenUpdating = True
End Sub |
Partager