For net borders

F

Faboboren

Hi,

I want to write a border only around A7:C11 and A12:C16 (in different
sheets), not in rows in between. I wrote down this code and I am getting
lines at every row..any idea how I should change that? Thanks

Sub Set_Borders()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
With sht.Range("A7:C11")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
End With
With sht.Range("A12:C16")
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders.Weight = xlMedium
.Borders.ColorIndex = xlAutomatic
End With
Next sht
End Sub
 
N

Nigel

Add the following two lines to the setting for the range(s)

.Borders(xlInsideVertical).LineStyle = xlNone
.Borders(xlInsideHorizontal).LineStyle = xlNone
 
C

Chip Pearson

You can use the BorderAround method to create a border around a range
of cells. E.g.,

Worksheets("Sheet1").Range("A7:C11").BorderAround _
LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic
Worksheets("Sheet2").Range("A12:C16").BorderAround _
LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
F

Faboboren

Thanks Nigel,

I am still getting borders at any single row, can you show me exactly where
I should insert those 2 lines?
 
F

Faboboren

Perfect, Thanks

Chip Pearson said:
You can use the BorderAround method to create a border around a range
of cells. E.g.,

Worksheets("Sheet1").Range("A7:C11").BorderAround _
LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic
Worksheets("Sheet2").Range("A12:C16").BorderAround _
LineStyle:=xlSolid, Weight:=xlMedium, ColorIndex:=xlAutomatic


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
F

Faboboren

Chip,

How I avoid to run the macros in my Macro sheet called Macros with these 4
lines?...

Sub Set_Borders()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
With sht.Range("A7:C11")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

help needed with borders 2
Exclude sheets from code 3
Border formatting row of cells 22
Loops through Sheets but doesn't work. 5
Cell Border 1
Help with macro 1
Do I need these lines? 5
Create a report page??? 2

Top