Ah oui désolé, j'ai été un peu trop vite !
Dans la feuille Calendrier, remplace la macro par ceci :
Private Sub Worksheet_Activate()
' en cas de changement direct dans BdD
With Sheets("BdD").ListObjects(1)
Application.EnableEvents = False
Range("_jour").ClearContents
Range("_nuit").ClearContents
For Each cel In .ListColumns("ID").DataBodyRange
If cel.Offset(0, 2) <> 0 And cel.Offset(0, 3) <> 0 Then Cells(cel.Offset(0, 2), cel.Offset(0, 3)) = cel.Offset(0, 1)
Next
Application.EnableEvents = True
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, id As String
With Sheets("BdD").ListObjects(1)
If Not Intersect(Target, Range("_jour")) Is Nothing Then
For Each cel In Intersect(Target, Range("_jour"))
id = Cells(cel.Row, "C") & "|" & Cells(8, cel.Column) * 1
Set c = .ListColumns("ID").Range.Find(id, LookIn:=xlValues)
If c Is Nothing Then
.ListRows.Add
.ListColumns("Nom").DataBodyRange.Rows(.ListRows.Count).Value = Cells(cel.Row, "C")
.ListColumns("Date").DataBodyRange.Rows(.ListRows.Count).Value = Cells(8, cel.Column)
.ListColumns("OFX").DataBodyRange.Rows(.ListRows.Count).Value = UCase(cel.Value)
Else
.ListColumns("OFX").DataBodyRange.Rows(c.Row - .HeaderRowRange.Row).Value = cel.Value
End If
Next
ElseIf Not Intersect(Target, Range("_nuit")) Is Nothing Then
For Each cel In Intersect(Target, Range("_nuit"))
id = Cells(cel.Row, "C") & "|" & Cells(8, cel.Column) * 1
Set c = .ListColumns("ID").Range.Find(id, LookIn:=xlValues)
If c Is Nothing Then
.ListRows.Add
.ListColumns("Nom").DataBodyRange.Rows(.ListRows.Count).Value = Cells(cel.Row, "C")
.ListColumns("Date").DataBodyRange.Rows(.ListRows.Count).Value = Cells(8, cel.Column)
.ListColumns("OFX").DataBodyRange.Rows(.ListRows.Count).Value = IIf(cel.Value = "", "", "N")
Else
.ListColumns("OFX").DataBodyRange.Rows(c.Row - .HeaderRowRange.Row).Value = IIf(cel.Value = "", "", "N")
End If
Next
ElseIf Not Intersect(Target, Range("C2:C3")) Is Nothing Then
Application.EnableEvents = False
Range("_jour").ClearContents
Range("_nuit").ClearContents
For Each cel In .ListColumns("ID").DataBodyRange
If cel.Offset(0, 2) <> 0 And cel.Offset(0, 3) <> 0 Then Cells(cel.Offset(0, 2), cel.Offset(0, 3)) = cel.Offset(0, 1)
Next
Application.EnableEvents = True
End If
End With
End Sub
Sub reactiver()
Application.EnableEvents = True
End Sub