Shorten Selection code

[Bobby Lai]

Good day.

I would like to know if I could shorten my code for formating various
cells:


Sub HighlightBox()

Dim rng As Range

Set rng = Selection

With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
End Sub

Thank u,
Mr Lai

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[William]

Hi Bobby

Sub HighlightBox()
Selection.Borders(xlInsideVertical).Weight = xlHairline
Selection.Borders(xlInsideHorizontal).Weight = xlHairline
Selection.BorderAround Weight:=xlThin, _
LineStyle:=xlContinuous, ColorIndex:=xlColorIndexAutomatic
End Sub


--
XL2002
Regards

William

(e-mail address removed)

| Good day.
|
| I would like to know if I could shorten my code for formating various
| cells:
|
|
| Sub HighlightBox()
|
| Dim rng As Range
|
| Set rng = Selection
|
| With Selection.Borders(xlEdgeLeft)
| .LineStyle = xlContinuous
| .Weight = xlThin
| .ColorIndex = xlAutomatic
| End With
| With Selection.Borders(xlEdgeTop)
| .LineStyle = xlContinuous
| .Weight = xlThin
| .ColorIndex = xlAutomatic
| End With
| With Selection.Borders(xlEdgeBottom)
| .LineStyle = xlContinuous
| .Weight = xlThin
| .ColorIndex = xlAutomatic
| End With
| With Selection.Borders(xlEdgeRight)
| .LineStyle = xlContinuous
| .Weight = xlThin
| .ColorIndex = xlAutomatic
| End With
| With Selection.Borders(xlInsideVertical)
| .LineStyle = xlContinuous
| .Weight = xlHairline
| .ColorIndex = xlAutomatic
| End With
| With Selection.Borders(xlInsideHorizontal)
| .LineStyle = xlContinuous
| .Weight = xlHairline
| .ColorIndex = xlAutomatic
| End With
| End Sub
|
| Thank u,
| Mr Lai
|
| ** Posted via: http://www.ozgrid.com
| Excel Templates, Training, Add-ins & Software!
| http://www.ozgrid.com/Services/excel-software-categories.htm **

 

[Trevor Shuttleworth]

Bobby

assuming weight and colorindex are previously not set to anything, you don't
need the default values so you can get away with:

Sub HighlightBox()
On Error Resume Next
With Selection
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End With
On Error GoTo 0
End Sub

Regards

Trevor

 

[Bobby Lai]

Many thanks to:
Trevor Shuttleworth
William

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Software!
http://www.ozgrid.com/Services/excel-software-categories.htm **