How to add a vertical thick line border to the entire column after every merged cell heading at the-CodePudding

Here is how I want it to have appeared? Please suggest a VBA code for this

Sub FormatTest()
    With Sheets("Test")
        With .Range("$B:$Z")
            .FormatConditions.Add xlExpression, Formula1:="=mod(row(),2)=0"
            With .FormatConditions(1).Borders(xlBottom)
                .LineStyle = xlContinuous
                .ColorIndex = xlAutomatic
                .TintAndShade = 0
                .Weight = xlThin
            End With
            .FormatConditions(1).StopIfTrue = False
        End With
    End With
End Sub

CodePudding user response：

This seems to work based on your screenshot. As remarked above, merged cells are generally problematic and should be avoided wherever possible.

Sub FormatTest()

Dim c As Long, r As Range

With Sheets("Test")
    For c = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column
        Set r = .Cells(1, c).MergeArea
        With Union(.Cells(1, c), .Columns(r(r.Columns.Count).Column))
            With .Borders(xlRight)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        End With
    Next c
End With

End Sub