Sub Macro1()
'
' Macro1 Macro
'
' Touche de raccourci du clavier: Ctrl+q

'suppression de toutes les fusions de cellule sur la feuille
    Cells.Select
    Selection.UnMerge
'creation du tableau de 300 lignes et de 6 colonne ( jusqu'a J )
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$6:$J$300"), , xlNo).Name = _
        "Tableau1"
    ActiveSheet.ListObjects("Tableau1").TableStyle = ""
'suppression de toutes les cases vide
    Cells.Select
    ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=1, Criteria1:= _
        "="
    Range("A8:A301").Select
    Selection.EntireRow.Delete
    ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=1
'Remplacement des textes pour plus de lisibilité
    Cells.Replace what:="05h - 13h", Replacement:="1-MATIN", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="05h - 13h (Si Samedi)", Replacement:="1-MATIN", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="05h - 12h", Replacement:="1-MATIN", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="1-MATIN (Si Samedi)", Replacement:="1-MATIN", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="13h - 21h", Replacement:="2-SOIR", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="19h - 02h", Replacement:="3-NUIT", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="21h - 05h", Replacement:="3-NUIT", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="12h - 19h", Replacement:="2-SOIR", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="CP", Replacement:="ABSENT", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="HRéc", Replacement:="ABSENT", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="RTT", Replacement:="ABSENT", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="MAL", Replacement:="ABSENT", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="Présence L/J", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="08h 12h - 13h30 17h", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="08h 12h - 13h30 16h", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="08h45-12h00 13h-16h45", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="8h45-12h00 13h-16h45", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="7h45-12h00 13h-16h45", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:=" 08h 12h - 12h45 16h15", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="08h 12h - 12h45 15h15", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="08h00 12h0 - 12h45 15h15", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="08h00 12h30 - 13h30 16h30", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:=" A Planifier L/J", Replacement:="5-A PLANIFIER", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:=" A Planifier V", Replacement:="5-A PLANIFIER", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="Présence V", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="AVALI", Replacement:="5-A VALIDER", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Cells.Replace what:="08h00 12h30 - 13h30 15h30", Replacement:="4-JOURNEE", LookAt:= _
        xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
'enleve les couleurs cellule et passe tous les textes en noir
    Range("Tableau1").Select
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("Tableau1").Select
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
'hauteur de cellule du tableau uniquement
    Range("Tableau1").Select
    Selection.RowHeight = 25
'largeur colonne
    Range("Tableau1").Select
    Selection.ColumnWidth = 28
'Tableau trié par ordre alphabetique
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields.Add _
        Key:=Range("Tableau1[[#All],[Colonne2]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'mise en reduit, et en transparence de l'entete tableau ( pour gardé que les filtres de visible )
    Rows("6:6").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.Font
        .Size = 4
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.RowHeight = 5.25
'ESSAIE DE MISE EN ORDRE SUR SEMAINE
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields.Add _
        Key:=Range("Tableau1[[#All],[Colonne3]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields.Add _
        Key:=Range("Tableau1[[#All],[Colonne4]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields.Add _
        Key:=Range("Tableau1[[#All],[Colonne5]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields.Add _
        Key:=Range("Tableau1[[#All],[Colonne6]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields. _
        Clear
    ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort.SortFields.Add _
        Key:=Range("Tableau1[[#All],[Colonne7]]"), SortOn:=xlSortOnValues, Order _
        :=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("sheet").ListObjects("Tableau1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'MISE EN FORME DE L'ENTETE
    Rows("1:1").Select
    Selection.RowHeight = 19.5
    Rows("3:3").Select
    Selection.RowHeight = 9
    Rows("4:4").Select
    Selection.RowHeight = 20.25
    Rows("5:5").Select
    Selection.RowHeight = 20.25
    Columns("A:A").Select
    Selection.ColumnWidth = 47
    Range("A4").Select
    ActiveCell.FormulaR1C1 = ""
'selection cell qui contienne le mot 1-MATIN
  Set champ = Nothing
  For Each c In [A1:J300]
    If c = "1-MATIN" Then
      If champ Is Nothing Then
        Set champ = c
      Else
        Set champ = Union(champ, c)
      End If
    End If
  Next c
  champ.Select
'mise en couleur
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With




'selection cell qui contienne le mot 2-SOIR
  Set champ = Nothing
  For Each c In [A1:J300]
    If c = "2-SOIR" Then
      If champ Is Nothing Then
        Set champ = c
      Else
        Set champ = Union(champ, c)
      End If
    End If
  Next c
  champ.Select
'mise en couleur
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    
    
    
    
    
'selection cell qui contienne le mot 3-NUIT
  Set champ = Nothing
  For Each c In [A1:J300]
    If c = "3-NUIT" Then
      If champ Is Nothing Then
        Set champ = c
      Else
        Set champ = Union(champ, c)
      End If
    End If
  Next c
  champ.Select
'mise en couleur
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    
'selection cell qui contienne le mot 4-JOURNEE
  Set champ = Nothing
  For Each c In [A1:J300]
    If c = "4-JOURNEE" Then
      If champ Is Nothing Then
        Set champ = c
      Else
        Set champ = Union(champ, c)
      End If
    End If
  Next c
  champ.Select
'mise en couleur
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With

'selection cell qui contienne le mot ABSENT
  Set champ = Nothing
  For Each c In [A1:J300]
    If c = "ABSENT" Then
      If champ Is Nothing Then
        Set champ = c
      Else
        Set champ = Union(champ, c)
      End If
    End If
  Next c
  champ.Select
'mise en couleur
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 192
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    
'selection cell qui contienne le mot 5-A PLANIFIER
  Set champ = Nothing
  For Each c In [A1:J300]
    If c = "5-A PLANIFIER" Then
      If champ Is Nothing Then
        Set champ = c
      Else
        Set champ = Union(champ, c)
      End If
    End If
  Next c
  champ.Select
'mise en couleur
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
    
    
'selection cell qui contienne le mot 5-A VALIDER
  Set champ = Nothing
  For Each c In [A1:J300]
    If c = "5-A VALIDER" Then
      If champ Is Nothing Then
        Set champ = c
      Else
        Set champ = Union(champ, c)
      End If
    End If
  Next c
  champ.Select
'mise en couleur
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = -0.349986266670736
        .PatternTintAndShade = 0
    End With
    
'mise en pointillié du tableau
    Range("Tableau1[#All]").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlDot
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlDot
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlDot
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
        .Weight = xlThin
    End With
    

End Sub