Macro to add Borders

[MJA001]

Hello,

I'm looking for a Macro to add Thick Outside Border to set width of columns, but will only extend down to the rows that are populated with info. In terms of my spreadsheet, The top of the Thick Outside Border will cover A5 to D5 and extend down for the cells that are populated. My gut tells me that this is an easy one. I just wish I was smart enough to figure it out.

Thanks in advance.

 

[Norie]

Perhaps something like this.

Sub AddThickBordersToCellsWithDataInThemInColumnsAToDFromRow5Down()
Dim LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    With Range("A5:D" & LastRow).Borders
        .LineStyle = xlContinuous
        .Weight = xlThick
    End With
    
End Sub

 

[BlissC]

If I'm understanding what you're trying to do correctly, you could use conditional formatting to achieve the same thing - it's similar to something I did earlier today.

Conditional Formatting > New Rule > New Formatting Rule

From the list of rule types, select the last type on the list "Use a formula to determine which cells to format". In the box where you enter the format, use this formula:

=$A1<>""

Set your border format to thick outside border as you normally would formatting a cell or cells and press okay. In the "applies to" box on the list of rules, apply the format to

[COLOR=#242729][FONT=Consolas]=$A:$D[/FONT][/COLOR]

.

Your formula needs to refer to the first row of the Applied To area. It sounds counter-intuitive if the area you want the border around starts on row 5, but your formula needs to refer to the first row of your 'applied to' area.

I had a set of columns where I wanted borders around every cell that's populated in columns B to T (in your case simply select the thick outside border in the 'format cells' window rather than the 'all borders' I used). My data rows start on row 4, as above row 4 I have a title and column headings, so I assumed that I needed to refer to B4, but that caused all sorts of weird results! Now with that set up, every cell that's populated in columns B to T from row 4 downwards has borders and every time I add an additional row of data, the borders extend downwards, but only to the last row that's populated.

It worked for me, so hopefully with that little tweak of the formatting it should work for you.

 

[MJA001]

Thanks! This worked great. How can I modify this to add the border to only the outside edge of the entire range?

 

[Norie]

For that you can use BorderAround.

Dim LastRow As Long

    LastRow = Range("A" & Rows.Count).End(xlUp).Row

    Range("A5:H" & LastRow).BorderAround LineStyle:=xlContinuous, Weight:=xlThick

 

[MJA001]

Awesome, thanks!

 

[rubenovik]

Double awesome! This macro is perfect for my needs too. Thank you