Mettre en forme des bordures par VBA

Re-bonjour,

Je tiens déjà à m'excuser, j'ai déjà fait un post totalement différent ce matin, qui a été résolu. Je ne savais pas s'il fallait que je poste cette demande à la suite du post, ou comme je le fais, sur un nouveau.

Je dois mettre en forme une feuille excel, au nombre de lignes variables. Voici le code. Quand je l'execute, il ne tiens pas du tout compte des bordures internes, des bordures hautes et basse, ni de la bordure de droite de la derniere section.

Le code est très long, je m'en excuse, mais très rébarbatif. Le but, c'est d'avoir un tableau encadré en "medium", avec a l'interieur des sections, définies par des bordures internes "medium" et le reste des bordures en "fin"

   
nblignes = Cells(Rows.Count, "A").End(xlUp).Row

    For i = 3 To nblignes
        If i = 3 Then
            With Range(Cells(i, 1), Cells(i, 9))
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlInsideVertical)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
            End With
            With Cells(i, 10)
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
            End With
            With Range(Cells(i, 11), Cells(i, 12))
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlInsideVertical)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
            End With
            With Range(Cells(i, 13), Cells(i, 14))
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlInsideVertical)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
            End With
        Else:
        If i = ligExport Then
                With Range(Cells(i, 1), Cells(i, 9))
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlInsideVertical)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                End With
                With Cells(i, 10)
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                End With
                With Range(Cells(i, 11), Cells(i, 12))
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlInsideVertical)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                End With
                With Range(Cells(i, 13), Cells(i, 14))
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlInsideVertical)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                End With
            Else: With Range(Cells(i, 1), Cells(i, 9))
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlInsideVertical)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                End With
                With Cells(i, 10)
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                End With
                With Range(Cells(i, 11), Cells(i, 12))
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlInsideVertical)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                End With
                With Range(Cells(i, 13), Cells(i, 14))
                    With .Borders(xlEdgeLeft)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeTop)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                    With selection.Borders(xlEdgeBottom)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlEdgeRight)
                        .LineStyle = xlContinuous
                        .Weight = xlMedium
                    End With
                    With selection.Borders(xlInsideVertical)
                        .LineStyle = xlContinuous
                        .Weight = xlThin
                    End With
                End With
            End If
        End If
    Next i

Bonjour,

Sans fichier difficile de comprendre ton code

Déjà je vois pas l'utilité du de " i " dans de cas là ? que tu utilises la variable "nblignes" pour la longueur du tableau ok

Mais pour la largeur ? tu ne connais pas non plus ton nombre de colonnes ?

Tu as essayés avec l'enregistreur macro ? suffira de remplacer par ta variable pour la longueur du tableau.

Bonjour,

Merci pour ton retour Xmenpl, j'ai résolu mon problème en te anonymisant le fichier.

Le i ne correspondait pas à la largeur mais bien à la ligne, il servait pour la boucle en disant de prendre la ligne 3 à la ligne "nblignes".

Mon problème venait de "selection." qui parasitait le truc, et ne mettait les bordures que sur la cellule sélectionnée. Je ne pouvais pas le voir sur mon fichier originel à cause du trop grand nombre de ligne.

En tout état de cause, je poste le fichier anonyme avec le code fonctionnel si quelqu'un d'autre venait à en avoir besoin et passe le sujet en résolu.