• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 19202
  • Last Modified:

VBA borders around all cells

Folks,
I am looking for a way to add borders around all cells using VBA for the range K3:L13. What I tried below doesn't seem to even give me the bottom edge.

Range("K3:L13").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous

Open in new window

0
Frank Freese
Asked:
Frank Freese
4 Solutions
 
Rgonzo1971Commented:
Hi,

pls try

Sub Macro7()
'
' Macro7 Macro
'

'
Set myrange = Range("K13:L13")
    myrange.Borders(xlDiagonalDown).LineStyle = xlNone
    myrange.Borders(xlDiagonalUp).LineStyle = xlNone
    With myrange.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With myrange.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
End Sub

Open in new window

Regards
0
 
Martin LissRetired ProgrammerCommented:
If you can do it by hand, record a macro while you are doing it.
0
 
zorvek (Kevin Jones)ConsultantCommented:
When working with multiple rows at the same time you have to set the inside horizontal border:

Range("K3:L13").Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
Range("K3:L13").Borders(xlInsideHorizontal).LineStyle = XlLineStyle.xlContinuous

Kevin
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Saqib Husain, SyedEngineerCommented:
Try

Range("K3:L13").Borders(xlEdgeBottom).LineStyle = xlContinuous
0
 
zorvek (Kevin Jones)ConsultantCommented:
Here is an elegant method for setting the borders around all individual cells in a multiple cell range:

   Dim BorderIndex As Variant
   For Each BorderIndex In Array(xlEdgeTop, xlEdgeLeft, xlEdgeBottom, xlEdgeRight, xlInsideHorizontal, xlInsideVertical)
      With TargetRange.Borders(BorderIndex)
         .Weight = xlThin
         .ColorIndex = xlAutomatic
      End With
   Next BorderIndex

In your case replace TargetRange with Range("K3:L13").

Kevin
1
 
Frank FreeseAuthor Commented:
Folks,
I wrote this and it worked! Surprised even myself.

Dim rng As Range
    Set rng = Range("K3:L13")
    With rng.Borders
        .LineStyle = xlContinuous
        .Color = vbBlack
        .Weight = xlThin
    End With

In fairness to all I'll distribute the points the points equally. I appreciate your response
0
 
Frank FreeseAuthor Commented:
thank you to all
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now