c'est un peu long mais voilà
For Each Cs In Synthese.Range("A1:N400")
If Cs.Value <> "" And Cs.Value <> 0 Then
Cs.Borders(xlDiagonalDown).LineStyle = xlNone
Cs.Borders(xlDiagonalUp).LineStyle = xlNone
With Cs.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Cs.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Cs.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
With Cs.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
Cs.Borders(xlInsideVertical).LineStyle = xlNone
Cs.Borders(xlInsideHorizontal).LineStyle = xlNone
End If
Next Cs
Range("A2").End(xlDown).Offset(0, 2).Select
Range(ActiveCell, ActiveCell.Offset(0, 4)).Merge
Range("A2").End(xlDown).Offset(1, 0).Select
Range(ActiveCell, ActiveCell.Offset(0, 6)).Merge
Range("A2").End(xlDown).Offset(2, 0).Select
ActiveCell.Value = "GROUPES EAU GLACEE"
Range(ActiveCell, ActiveCell.Offset(0, 6)).Merge
With Selection.Interior
.PatternColorIndex = xlAutomatic
.color = 6684876
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A2").End(xlDown).Offset(3, 0).Select
ActiveCell.Value = "MODELES DE GROUPE EAU GLACEE PROPOSES"
Range(ActiveCell, ActiveCell.Offset(0, 3)).Merge
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Range("A2").End(xlDown).Offset(3, 4).Select
ActiveCell.Value = "PUISSANCES FRIGORIFIQUES NOMINALES (kW)"
Range(ActiveCell, ActiveCell.Offset(0, 2)).Merge
With Selection.Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorLight2
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
Range("A2").End(xlDown).Offset(4, 0).Select
Btherm = Application.Sum(Range("B2").EntireColumn)
ActiveCell.FormulaArray = _
"=INDEX(Modele_Groupe,MATCH(TRUE,Puiss_Groupe>(" & Range("B2").End(xlDown).Offset(0, 0).Address & "),0))"
Range(ActiveCell, ActiveCell.Offset(0, 3)).Merge
Range("A2").End(xlDown).Offset(4, 4).Select
ActiveCell.FormulaR1C1 = _
"=INDEX(Puiss_Groupe,MATCH(R10C1,Modele_Groupe,0))"
Range(ActiveCell, ActiveCell.Offset(0, 2)).Merge
Range("A1:G1").Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent5
.TintAndShade = 0.399975585192419
.PatternTintAndShade = 0
End With