Vba Add Vertical Borders With An Exclusion

[Lewzerrrr]

Hey,

I have a format data selection code. What I need now from it is to:
Add vertical borders across each column in the selected range.
Horizontal AND vertical across row(1) and last row of selected range.
Throughout the selected range, if a cell is blank I don't want a vertical border E.G. \ = vertical border. Notice Subtotal & Total both B4:C4 and B6:C6 are blank so they have no vertical borders.

[TABLE="width: 448"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]F[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]\SUBTOTAL[/TD]
[TD]""[/TD]
[TD]""[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]\TOTAL[/TD]
[TD]""[/TD]
[TD]""[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[TD]\6[/TD]
[/TR]
</tbody>[/TABLE]


Code:

Sub FormatData()

Dim Rng As Range


    Set Rng = Selection


    With Rng
    
        .Borders.LineStyle = xlNone
        .Font.Name = "Calibri"


        With .Rows(1)
            
            .Interior.ThemeColor = xlThemeColorAccent1
            .Interior.TintAndShade = -0.499984740745262
            .Font.Bold = True
            .Font.Size = 10
            .Font.ThemeColor = xlThemeColorDark1
            .WrapText = True
            
        End With


        With .Rows(2).Resize(.Rows.Count - 2)
        
            .FormatConditions.Delete
            .FormatConditions.Add Type:=xlExpression, Formula1:= _
                                  "=MOD(ROW(),2)=0"
            .FormatConditions(.FormatConditions.Count).SetFirstPriority


                With .FormatConditions(.FormatConditions.Count)
                
                    .Interior.PatternColorIndex = xlAutomatic
                    .Interior.ThemeColor = xlThemeColorAccent1
                    .Interior.TintAndShade = 0.799981688894314
                    
                End With
            
        End With
        
        With .Rows(2).Resize(.Rows.Count - 2)
        
            .Font.Size = 8
        
        End With


        With .Rows(.Rows.Count)
            .FormatConditions.Delete
            .Font.Bold = True
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
            .Font.Size = 10
            .Interior.ThemeColor = xlThemeColorAccent1
            .Interior.TintAndShade = -0.499984740745262
            .Interior.PatternTintAndShade = 0
        End With


    End With


End Sub

 

[Fluff]

Try adding the line in red

       With .Rows(2).Resize(.Rows.Count - 2)
        
            .Font.Size = 8
        
        End With

       [COLOR=#ff0000].SpecialCells(xlBlanks).Borders(xlEdgeLeft).LineStyle = xlNone[/COLOR]

        With .Rows(.Rows.Count)
            .FormatConditions.Delete
            .Font.Bold = True
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
            .Font.Size = 10
            .Interior.ThemeColor = xlThemeColorAccent1
            .Interior.TintAndShade = -0.499984740745262
            .Interior.PatternTintAndShade = 0
        End With

 

[Lewzerrrr]

Try adding the line in red

       With .Rows(2).Resize(.Rows.Count - 2)
        
            .Font.Size = 8
        
        End With

       [COLOR=#ff0000].SpecialCells(xlBlanks).Borders(xlEdgeLeft).LineStyle = xlNone[/COLOR]

        With .Rows(.Rows.Count)
            .FormatConditions.Delete
            .Font.Bold = True
            .Font.ThemeColor = xlThemeColorDark1
            .Font.TintAndShade = 0
            .Font.Size = 10
            .Interior.ThemeColor = xlThemeColorAccent1
            .Interior.TintAndShade = -0.499984740745262
            .Interior.PatternTintAndShade = 0
        End With

Tried this but no borders were added anywhere. I just typed up this code so will incorporate it in. Thanks so much

    For Each c In rng        If c <> "" Then
            With c.Borders
                .LineStyle = xlcontinous
                .Color = vbBlack
                .Weight = xlThin
            End With
        End If
    Next

Just seen the flaw in this ahaha, I would still need outside borders around all of the data.. I can add that