ExcelBanter - Border Formats

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Border Formats (https://www.excelbanter.com/excel-programming/348505-border-formats.html)

Dave[_54_]

Border Formats

Hi All
To format the outer borders of a selection, I use this code:

A = 3
Range("C34:J42").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With

It's very cumbersome, but I have had no success trying to compact it.
Is it possible to combine the 4 borders into a single statement, and then
apply the 3 format lines just once?

Regards - Dave.

Tom Ogilvy

Border Formats

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3

End Sub

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...
Hi All
To format the outer borders of a selection, I use this code:

A = 3
Range("C34:J42").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With

It's very cumbersome, but I have had no success trying to compact it.
Is it possible to combine the 4 borders into a single statement, and then
apply the 3 format lines just once?

Regards - Dave.

Rowan Drummond[_3_]

Border Formats

Try:

Const A As Long = 3
Range("C34:J42").BorderAround xlContinuous, xlMedium, A

Hope this helps
Rowan

Dave wrote:
Hi All
To format the outer borders of a selection, I use this code:

A = 3
Range("C34:J42").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With

It's very cumbersome, but I have had no success trying to compact it.
Is it possible to combine the 4 borders into a single statement, and then
apply the 3 format lines just once?

Regards - Dave.

Dave[_54_]

Border Formats

Thanks very much. Exactly what I was after. I used the macro recorder to
do it originally, and that was the code it produced.

Dave.

"Tom Ogilvy" wrote in message
...
Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3

End Sub

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...
Hi All
To format the outer borders of a selection, I use this code:

A = 3
Range("C34:J42").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With

It's very cumbersome, but I have had no success trying to compact it.
Is it possible to combine the 4 borders into a single statement, and
then
apply the 3 format lines just once?

Regards - Dave.

Desert Piranha[_21_]

Border Formats

Hi Tom,
If this works:

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3
End Sub

Then why won't this work?

Sub abcd()
With Selection.BorderAround _
Weight:=xlMedium, ColorIndex:=3
End With
End Sub

Tom Ogilvy Wrote:
Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3

End Sub

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...
Hi All
To format the outer borders of a selection, I use this code:

A = 3
Range("C34:J42").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With

It's very cumbersome, but I have had no success trying to compac
it.
Is it possible to combine the 4 borders into a single statement, an
then
apply the 3 format lines just once?

Regards - Dave.

--
Desert Piranh

-----------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...fo&userid=2893
View this thread: http://www.excelforum.com/showthread.php?threadid=49477

Rowan Drummond[_3_]

Border Formats

Try:

With Selection
.BorderAround Weight:=xlMedium, ColorIndex:=3
End With

Regards
Rowan

Desert Piranha wrote:
Hi Tom,
If this works:

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3
End Sub

Then why won't this work?

Sub abcd()
With Selection.BorderAround _
Weight:=xlMedium, ColorIndex:=3
End With
End Sub

Tom Ogilvy Wrote:

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3

End Sub

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...

Hi All
To format the outer borders of a selection, I use this code:

A = 3
Range("C34:J42").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With

It's very cumbersome, but I have had no success trying to compact

it.

Is it possible to combine the 4 borders into a single statement, and

then

apply the 3 format lines just once?

Regards - Dave.

Desert Piranha[_22_]

Border Formats

Hi Rowan,

Thx, i knew it was something stupid that i was doing wrong.

Thx Again
Dave
Rowan Drummond Wrote:
Try:

With Selection
.BorderAround Weight:=xlMedium, ColorIndex:=3
End With

Regards
Rowan

Desert Piranha wrote:
Hi Tom,
If this works:

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3
End Sub

Then why won't this work?

Sub abcd()
With Selection.BorderAround _
Weight:=xlMedium, ColorIndex:=3
End With
End Sub

Tom Ogilvy Wrote:

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3

End Sub

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...

Hi All
To format the outer borders of a selection, I use this code:

A = 3
Range("C34:J42").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With

It's very cumbersome, but I have had no success trying to compact

it.

Is it possible to combine the 4 borders into a single statement,
and

then

apply the 3 format lines just once?

Regards - Dave.

--
Desert Piranha

------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=494774

Peter Rooney

Border Formats

Rowan,

What's the quickest way to remove ALL border formatting from a range i.e.
top right, left, buttom, inside horizontal and vertical and diagonals?

Cheers

Pete

"Rowan Drummond" wrote:

Try:

With Selection
.BorderAround Weight:=xlMedium, ColorIndex:=3
End With

Regards
Rowan

Desert Piranha wrote:
Hi Tom,
If this works:

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3
End Sub

Then why won't this work?

Sub abcd()
With Selection.BorderAround _
Weight:=xlMedium, ColorIndex:=3
End With
End Sub

Tom Ogilvy Wrote:

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3

End Sub

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...

Hi All
To format the outer borders of a selection, I use this code:

A = 3
Range("C34:J42").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With

It's very cumbersome, but I have had no success trying to compact

it.

Is it possible to combine the 4 borders into a single statement, and

then

apply the 3 format lines just once?

Regards - Dave.

Paul B

Border Formats

Our just,
Selection.BorderAround _
Weight:=xlMedium, ColorIndex:=3

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Desert Piranha"
<Desert.Piranha.20bmvy_1135053000.9335@excelforu m-nospam.com wrote in
message news:Desert.Piranha.20bmvy_1135053000.9335@excelfo rum-nospam.com...

Hi Rowan,

Thx, i knew it was something stupid that i was doing wrong.

Thx Again
Dave
Rowan Drummond Wrote:
Try:

With Selection
.BorderAround Weight:=xlMedium, ColorIndex:=3
End With

Regards
Rowan

Desert Piranha wrote:
Hi Tom,
If this works:

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3
End Sub

Then why won't this work?

Sub abcd()
With Selection.BorderAround _
Weight:=xlMedium, ColorIndex:=3
End With
End Sub

Tom Ogilvy Wrote:

Sub abcd()
Range("C34:J42").BorderAround _
Weight:=xlMedium, ColorIndex:=3

End Sub

--
Regards,
Tom Ogilvy

"Dave" wrote in message
...

Hi All
To format the outer borders of a selection, I use this code:

A = 3
Range("C34:J42").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = A
End With

It's very cumbersome, but I have had no success trying to compact

it.

Is it possible to combine the 4 borders into a single statement,
and

then

apply the 3 format lines just once?

Regards - Dave.

--
Desert Piranha

------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=494774

Peter T

Border Formats

'set rng = selection
rng.Borders.LineStyle = xlNone

Regards,
Peter T

"Peter Rooney" wrote in message
...
Rowan,

What's the quickest way to remove ALL border formatting from a range i.e.
top right, left, buttom, inside horizontal and vertical and diagonals?

Cheers

Pete
<snip

Peter Rooney

Border Formats

Peter,

Spot on - thanks a lot!

Have a good Christmas, too.

Pete

"Peter T" wrote:

'set rng = selection
rng.Borders.LineStyle = xlNone

Regards,
Peter T

"Peter Rooney" wrote in message
...
Rowan,

What's the quickest way to remove ALL border formatting from a range i.e.
top right, left, buttom, inside horizontal and vertical and diagonals?

Cheers

Pete
<snip

All times are GMT +1. The time now is 05:04 AM.