How to add a bottom border along row that has merged cells with VBA formatConditons

Question

I have a sheet where in column B the cells are merged with the row below. In the rest of the columns the rows are not merged.

I want to add a VBA code that draws a line across the bottom of the entire row along the bottom of the merged cells. It's as if I wanted to draw a bottom border every other row for all columns (except B where each merged cell would have the bottom border). I have tried using the following code but the border is not drawn under the merged cells

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

Here is an example of what i want to achieve

I want to achieve this with conditional formatting because the number of rows will change from time to time and i don't want to have borders on empty cells.

The photo is just an example because there are many rows and on different sheets I will have a different number of columns so I just want to apply it to the whole row... Can anyone help?

Kubie Kubie · Accepted Answer · 2018-12-07T15:09:07

You can try using something along these lines:

(Just might need to tinker with the row = 1 to get the correct starting position)

Dim row As Long
Dim lastRow As Long
Dim lCol As Long
Dim letter As String

With ThisWorkbook.Worksheets("your sheet name")
    ' clear previous borders
    .Range("A5:ZZ30000").Borders.Linestyle = xlNone
    ' add new borders
    lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    lCol = .Cells(1, .Columns.Count).End(xlToLeft).Column ' checks row 1 for last col
    letter = Split(.Cells(1, lCol).Address, "$")(1)
    For row = 1 To lastRow+1 Step 2
        With .Range("B" & row & letter & row).Borders(xlBottom)
            .LineStyle = xlContinuous
            .Weight = xlThin
        End With  
    Next
End With

How to add a bottom border along row that has merged cells with VBA formatConditons

1 Answers