Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) On Error GoTo ErreurStop
If Target.Row > 16 Then Exit Sub
If Target.Column > 35 Then Exit Sub
Select Case Target.Value
Case Is = 1
Target.HorizontalAlignment = xlLeft
Case Is = "N"
Target.HorizontalAlignment = xlCenter
Case Is = 2
Target.HorizontalAlignment = xlRight
End Select
ErreurStop:
End Sub
Option Explicit
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Const Sauf = "recap;toto" 'inscrire les noms des feuilles pas concernées
Dim Tsauf, T(1 To 32), i&, j&, n&, aux
With Sh
' vérif si on doit continuer (feuilles exclues)
Tsauf = Split(Sauf, ";")
For i = 0 To UBound(Tsauf)
If UCase(Sh.Name) = UCase(Tsauf(i)) Then Exit Sub
Next i
' si on a clique sur une cellule de lacolonne F des lignes 2 à 15
If Not Intersect(Target, .Range("f2:f15")) Is Nothing Then
Application.ScreenUpdating = False
Cancel = True
' on remplit un tableau T avec les nombres de 1, "N", 2 désirés
For i = 2 To 0 Step -1
For j = 1 To Target.Offset(0, -i - 1)
n = n + 1: T(n) = Array(1, "N", 2)(2 - i)
Next j
Next i
' on mélange le tableau T
Randomize
For i = 1 To 32: j = 1 + Int(32 * Rnd): aux = T(i): T(i) = T(j): T(j) = aux: Next
' on écrit la tableau sur la ligne
Target.Offset(, 1).Resize(, 32) = T
'on boucle sur la ligne pour les alignements
For i = 0 To 31
With .Cells(Target.Row, "g").Offset(, i)
Select Case .Value
Case 1: .HorizontalAlignment = xlLeft
Case "N": .HorizontalAlignment = xlCenter
Case "2": .HorizontalAlignment = xlRight
End Select
End With
Next i
Application.ScreenUpdating = True
End If
End With
End Sub