Sub Dispo()
Dim TE(), LE As Long, CE As Long, TS(), LS As Long, CS As Long, TLC() As Long, FSource As Worksheet
Dim DerL As Long
Sheets("Dispo").Range("B4:CP60").ClearContents
Set FSource = ThisWorkbook.Sheets("BDD9")
DerL = FSource.Range("A" & Rows.Count).End(xlUp).Row
TE = FSource.Range("A2:AH" & DerL) 'ligne modifiée
ReDim TS(1 To UBound(TE, 1) \ 3 + 1, 1 To UBound(TE, 2) * 3 - 2)
ReDim TLC(1 To UBound(TS, 2))
For LE = 2 To UBound(TE, 1)
For CE = 2 To UBound(TE, 2)
Select Case TE(LE, CE)
Case "M": CS = CE * 3 - 5
Case "A": CS = CE * 3 - 4
Case "N": CS = CE * 3 - 3
Case "Pro": CS = CE * 3 + (LE - 2) Mod 3 - 5 'And Range(LE).Interior.ColorIndex = 8
Case "BIP": CS = CE * 3 + (LE - 2) Mod 3 - 5
Case Else: CS = 0: End Select
If CS > 0 Then
LS = TLC(CS) + 1: TLC(CS) = LS
'ligne modifiée: insertion de "PRO" pour les individus Pro
TS(LS, CS) = TE(LE - (LE - 2) Mod 3, 34) & IIf(TE(LE - (LE - 2) Mod 3, CE) = "Pro", " PRO", "")
End If: Next CE, LE
Sheets("Dispo").[B4].Resize(UBound(TS, 1), UBound(TS, 2)).Value = TS
'** recherche des "PRO" et modif couleur et suppression de la mention "PRO"
For Each cel In Sheets("Dispo").Range("B4").CurrentRegion.SpecialCells(xlCellTypeConstants)
If cel.Value Like "*PRO" Then
cel.Interior.ColorIndex = 5
cel.Font.ColorIndex = 2
cel.Value = Left(cel.Value, Len(cel) - 4)
End If
Next
MsgBox "Mise à Jour terminée avec succées cliquez sur (OK) pour continuer"
'Menu.Caption = "Menu"
'Unload Menu
End Sub