Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 'n'est plus Private...
If Target.Address <> "$A$5" Then Exit Sub
Cancel = True: _
Application.ScreenUpdating = False
Feuil3.UsedRange.Name = "RécapCA" 'nom défini dans le classeur
With [B5].Resize(2, Cells(2, Columns.Count).End(xlToLeft).Column)
.Rows(1) = "=IF(COUNTA(B3:B4),COUNTA(B3:B4)/2,"""")" 'ligne 5
.Rows(2) = "=1/COUNTIF(RécapCA,B2)" 'ligne 6 (auxiliaire)
With .Rows(-3).Resize(5) 'lignes 1:5
.Interior.ColorIndex = xlNone 'effacement des couleurs de fond
On Error Resume Next 's'il n'y a pas de dates en Feuil3
Intersect(.Cells, .Rows(6).SpecialCells(xlCellTypeFormulas, 1).EntireColumn).Interior.Color = 14540253 'gris clair
.Rows(6) = "" 'RAZ ligne 6
End With
End With
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Areas.Count > 1 Or Target.Row > 1 Or Target.Column = 1 Or Target.Rows.Count <> 5 Or Target(1) = "" Then Exit Sub
Application.EnableEvents = False 'évite de lancer la Worksheet_Change et la Worksheet_BeforeDoubleClick
With Feuil3.Range("D" & Feuil3.Rows.Count).End(xlUp)(2, 2).Resize(5, Target.Columns.Count)
Target.Copy .Cells
.Cells = .Cells.Value 'supprime les formules
.Cells(5, 1).Copy .Cells(5, 0) 'pour copier les formats
.Cells(5, 0) = "=IF(RC[1]="""","""",SUM(" & .Rows(5).Address(0, 0, xlR1C1, , .Cells(5, 0)) & "))"
.Cells(5, 0).Borders.Weight = xlThin
.Borders(xlEdgeLeft).Weight = xlThin 'c'est mieux
.Borders(xlEdgeRight).Weight = xlThin 'c'est mieux
.Parent.Activate 'facultatif
End With
Target.Interior.Color = 14540253 'gris clair
Application.EnableEvents = True
End Sub