Bonjour,
Dans la solution ci-dessous, le principe est de travailler avec un tableau structuré.
Option Explicit
Sub TestMettreAJourOnglet()
Dim TabDonnees As ListObject
Dim AireTableau As Range
With Sheets("Question Date2")
.Activate
If .ListObjects.Count = 0 Then
Set AireTableau = .Range("A1").CurrentRegion
Set TabDonnees = .ListObjects.Add(xlSrcRange, AireTableau, , xlYes)
End If
MettreAJourOnglet ActiveSheet, "797326"
End With
Set AireTableau = Nothing: Set TabDonnees = Nothing
End Sub
Sub MettreAJourOnglet(ByVal Sh As Worksheet, ByVal NumCB As String)
Dim J As Integer, MoisEnCours As Integer
Dim AireEtat As Range, AireDateDebut As Range, AireCBEx As Range
Application.ScreenUpdating = False
With Sh.ListObjects(1)
Set AireEtat = .ListColumns("Etat").DataBodyRange
Set AireCBEx = .ListColumns("CB exéc. Prest.").DataBodyRange
Set AireDateDebut = .ListColumns("Date début").DataBodyRange
End With
MoisEnCours = Month(Date)
'Sous Traitance Gestionnaire
For J = AireEtat.Count To 1 Step -1
' Pour vérifier
' If AireEtat(J) = "AT" Then AireEtat(J).Interior.Color = RGB(255, 255, 0)
' If AireCBEx(J) = CStr(NumCB) Then AireCBEx(J).Interior.Color = RGB(255, 255, 0)
' If Month(CDate(AireDateDebut(J))) < MoisEnCours Then AireDateDebut(J).Interior.Color = RGB(255, 255, 0)
If AireEtat(J) = "AT" Then AireEtat(J).EntireRow.Delete
If AireCBEx(J) = CStr(NumCB) Then AireEtat(J).EntireRow.Delete
If Month(CDate(AireDateDebut(J))) < MoisEnCours Then AireEtat(J).EntireRow.Delete
Next J
With Sh.ListObjects(1)
.Sort.SortFields.Clear
.Sort.SortFields.Add2 Key:=AireDateDebut, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Set AireEtat = Nothing: Set AireCBEx = Nothing: Set AireDateDebut = Nothing
Application.ScreenUpdating = True
End Sub