Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With [D10:AG40] 'à adapter
If Intersect(Target, .Cells) Is Nothing Or Target(1) = "" Then Exit Sub
Dim a, d$
Cancel = True
'---format nombre personnalisé---
a = [{"","C";"C","M";"M",""}]
d = Right(Target(1).Text, 1)
If d <> "C" And d <> "M" Then d = ""
d = Application.VLookup(d, a, 2, 0) 'rotation
Target.NumberFormat = "0.00 """ & d & """"
'---poids total et plus gros poisson---
Worksheet_Change Target 'lance la macro
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, a, c As Range, maxC, ligC&, maxM, ligM&
Application.EnableEvents = False 'désactive les évènements
With [D10:AG40] 'à adapter
'---poids total C et M---
Set r = Intersect(Target.EntireRow, .Cells)
If Not r Is Nothing Then
For Each r In r.Rows 'si entrées/effacements multiples
ReDim a(1 To 2)
For Each c In r.Cells
If c Like "*C" Or c Like "*M" Then c = "" 'sécurité
If c.Text Like "*C" Then a(1) = a(1) + c
If c.Text Like "*M" Then a(2) = a(2) + c
Next c
Intersect(r.EntireRow, [AL:AM]) = a
Next r
End If
'---plus gros poisson C et M---
For Each c In .Cells
If c.Text Like "*C" Then If c > maxC Then maxC = c: ligC = c.Row
If c.Text Like "*M" Then If c > maxM Then maxM = c: ligM = c.Row
Next
With [A45:AG46] 'à adapter
.Resize(, .Columns.Count + 1) = "" 'RAZ
If ligC Then .EntireColumn.Rows(ligC).Copy .Cells(1): .Cells(1, .Columns.Count + 1) = maxC
If ligM Then .EntireColumn.Rows(ligM).Copy .Cells(2, 1): .Cells(2, .Columns.Count + 1) = maxM
.Resize(, .Columns.Count + 1).Borders.Weight = xlThin 'bordures
End With
End With
Application.EnableEvents = True 'réactive les évènements
End Sub