XL 2021 Planning chantier avec SERIE.JOUR.OUVRE.INTL

Function FIN(deb As Range, duree As Range, fer As Range, avance As Range)
Dim jour&, n&
On Error GoTo 1
For jour = deb To deb + 10000
    If Weekday(jour, 2) < 6 And Application.CountIf(fer, jour) = 0 Then n = n + 1
    If n = duree Then FIN = jour - avance: Exit For
Next
1 If FIN = 0 Then FIN = ""
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fer As Range, avance&, n&, com$, jour&
With ListObjects(1).Range 'tableau structuré
    Set Target = Intersect(Target, .Cells)
    If Target Is Nothing Then Exit Sub
    Set Target = Intersect(Target.EntireRow, .Columns(7))
End With
Application.EnableEvents = False 'désactive les évènements
Target.Offset(, -1).ClearComments 'RAZ
Set fer = [Fériés]
For Each Target In Target 'si entrées ou effacements multiples
    If IsDate(Target.Offset(, -1)) Then
        avance = Abs(Int(Val(Target))): Target = IIf(avance, avance, "")
        If Target.Offset(, -1) < Target.Offset(, -3) + Target.Offset(, -2) - 1 Then
            Target.Select
            MsgBox "L'avance est trop grande et va être effacée !", 48
            Target = ""
        End If
        If Target > 0 Then
            n = 0: com = "Travail :"
            For jour = Target.Offset(, -1) + avance To 1 Step -1
                If Weekday(jour, 2) > 5 Or Application.CountIf(fer, jour) Then
                    n = n + 1
                    com = com & vbLf & "- " & Format(jour, "ddd dd/mm/yyyy")
                End If
                If n = avance Then Exit For
            Next jour
            With Target(1, 0).AddComment(com).Shape.TextFrame
                .Characters.Font.Name = "Courier New" 'chasse fixe
                .AutoSize = True 'ajuste la taille
            End With
        End If
    End If
Next Target
Application.EnableEvents = True 'réactive les évènements
End Sub