Add a bottom border to last merge row in this VB Code?

[slam]

The code below merges a number of cells in column A and V. How would I also have it add a bottom border to the last row in each cell merge?

For instance, it may merge A3:A9 and V3:V9. I would want a bottom border added to A9 through V9. Also, how would I specify the color of the border?

Thanks


On Error GoTo safeExit
headerRow = 1
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
f = headerRow + 1
Application.DisplayAlerts = False
Do Until f > lastRow
l = f + 1
Do Until (Cells(l, 1) <> Cells(l - 1, 1)) Or (Cells(l, 22) <> Cells(l - 1, 22))
l = l + 1
Loop
If f <> l Then
For c = 1 To 22 Step 21
With Range(Cells(f, c), Cells(l - 1, c))
.merge
.VerticalAlignment = xlCenter
End With
Next c
End If
f = l
Loop
safeExit:
Application.DisplayAlerts = True

 

[jbeaucaire]

Does this do it? Or at least get you started in the right direction?

                With Range(Cells(f, c), Cells(l - 1, c))
                    .Merge
                    .VerticalAlignment = xlCenter
                    .Borders(xlEdgeBottom).Weight = xlMedium
                    .Borders(xlEdgeBottom).ColorIndex = 3
                End With

 

[slam]

I added that after Application.DisplayAlerts = True

Isn't applying a green border anywhere though

 

[jbeaucaire]

It wasn't really meant to go there. I included the "snippet" of your original code and highlighted what/where I added. The "3" in that code will make a red border. I think "50" will give you a green... play with the numbers to get the colors you want.

 

[slam]

It wasn't really meant to go there. I included the "snippet" of your original code and highlighted what/where I added. The "3" in that code will make a red border. I think "50" will give you a green... play with the numbers to get the colors you want.

Oops! Sorry, that was pretty obvious if I'd just slowed down.

Red it is - I tried looking up the codes online and on what I was looking at 3 was green?

Not sure if I didn't explain it well though. That does indeed highlight the bottom of the last cell in the merge, but only in columns A and V. I need the same bottom border on all the cells in between as well.

 

[slam]

Oops! Sorry, that was pretty obvious if I'd just slowed down.

Red it is - I tried looking up the codes online and on what I was looking at 3 was green?

Not sure if I didn't explain it well though. That does indeed highlight the bottom of the last cell in the merge, but only in columns A and V. I need the same bottom border on all the cells in between as well.

Didn't mean highlight - meant that it does indeed add a border to the bottom of the last cell in the merge....

 

[jbeaucaire]

I'm struggling to grasp what/where you code is acting. This is a stab in the dark.... hopefully if wrong enough to spark your imagination on how to do it in the correct spot...

        If f <> l Then
            For c = 1 To 22 Step 21
                With Range(Cells(f, c), Cells(l - 1, c))
                    .Merge
                    .VerticalAlignment = xlCenter
                End With
            Next c
[COLOR="Red"]            With .Range(Cells(l - 1, 1), Cells(l - 1, 22))
                .Borders(xlEdgeBottom).Weight = xlMedium
                .Borders(xlEdgeBottom).ColorIndex = 50
            End With
[/COLOR]        End If

 

[slam]

Had to change "With .Range" to "With Range", but That is almost it!

It's adding the border correctly where needed across the entire row with one exception.

The only issue is that its adding a border to the bottom of B2 through U2 (not column A or V though) which should not be there. In my spreadsheet A2:A3 and V2:V3 are merged, so the first border on the spreadsheet should be at the bottom of A3 through V3.

Again, row 1 is the header row (and I can manually add a border to the bottom of that row, so no need for the macro to add it).

Thanks

 

[slam]

Had to change "With .Range" to "With Range", but That is almost it!

It's adding the border correctly where needed across the entire row with one exception.

The only issue is that its adding a border to the bottom of B2 through U2 (not column A or V though) which should not be there. In my spreadsheet A2:A3 and V2:V3 are merged, so the first border on the spreadsheet should be at the bottom of A3 through V3.

Again, row 1 is the header row (and I can manually add a border to the bottom of that row, so no need for the macro to add it).

Thanks

UPDATE:

When I ran it again with some different data, it added the extra border in B through U in a few different places......

PM me your e-mail address if you'd like me to send you the actual spreadsheet. Thanks