Border Help Please

[JBShandrew]

I am trying to shorten the amount of lines I need to write to put a border around a range of cells, including the inside vertical and horizontal lines.

This works




With Range("$A$4:$H$36").Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = RGB(0, 0, 255)
End With

With Range("$A$4:$H$36").Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Color = RGB(0, 0, 235)
End With

With Range("$A$4:$H$36").Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Color = RGB(0, 0, 235)
End With

</PRE>



This does not work. Is there a similar way to this to have Excel draw all of the borders that I want?







with range("I2":K2").borders
.xlEdgeRight LineStyle:=xlContinuous, Color:=RGB(0, 0, 255)
.xlEdgeTop LineStyle:=xlContinuous, Color:=RGB(0, 0, 255)
.xlEdgeBottom LineStyle:=xlContinuous, Color:=RGB(0, 0, 255)
.xlInsideHorizontal LineStyle:=xlContinuous, Color:=RGB(0, 0, 255)
.xlInsideVertical LineStyle:=xlContinuous, Color:=RGB(0, 0, 255)
End With

</PRE>



Thank you in advance for the expert help,

Sincerely,

J.B.

 

[AlphaFrog]

<font face=Courier New><SPAN style="color:#007F00">' Values of xlConstants for borders</SPAN><br><SPAN style="color:#007F00">'xlDiagonalDown = 5</SPAN><br><SPAN style="color:#007F00">'xlDiagonalUp = 6</SPAN><br><SPAN style="color:#007F00">'xlEdgeLeft = 7</SPAN><br><SPAN style="color:#007F00">'xlEdgeTop = 8</SPAN><br><SPAN style="color:#007F00">'xlEdgeBottom = 9</SPAN><br><SPAN style="color:#007F00">'xlEdgeRight = 10</SPAN><br><SPAN style="color:#007F00">'xlInsideVertical = 11</SPAN><br><SPAN style="color:#007F00">'xlInsideHorizontal = 12</SPAN><br><br><SPAN style="color:#00007F">For</SPAN> i = 7 <SPAN style="color:#00007F">To</SPAN> 12<br>    <SPAN style="color:#00007F">With</SPAN> Range("$A$4:$H$36").Borders(i)<br>        .LineStyle = xlContinuous<br>        .Color = RGB(0, 0, 255)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">Next</SPAN> i</FONT>

 

[JBShandrew]

' Values of xlConstants for borders
'xlDiagonalDown = 5
'xlDiagonalUp = 6
'xlEdgeLeft = 7
'xlEdgeTop = 8
'xlEdgeBottom = 9
'xlEdgeRight = 10
'xlInsideVertical = 11
'xlInsideHorizontal = 12

For i = 7 To 12
With Range("$A$4:$H$36").Borders(i)
.LineStyle = xlContinuous
.Color = RGB(0, 0, 255)
End With
Next i

Thank for for the quick responce. I am new to VBA. I am getting a veriable not defined error. Do I use DIM i as Range?

 

[AlphaFrog]

Dim i As Integer

 

[JBShandrew]

' Values of xlConstants for borders
'xlDiagonalDown = 5
'xlDiagonalUp = 6
'xlEdgeLeft = 7
'xlEdgeTop = 8
'xlEdgeBottom = 9
'xlEdgeRight = 10
'xlInsideVertical = 11
'xlInsideHorizontal = 12

For i = 7 To 12
With Range("$A$4:$H$36").Borders(i)
.LineStyle = xlContinuous
.Color = RGB(0, 0, 255)
End With
Next i

Thank for for the quick responce. I am new to VBA. Can you please tell me what 7 to 12 means? Is that the number of border types xl_ _ _ ?

I think I got it from your post above. 7 to 12 is the line number style that corresponds to the style.
xlEdgeLeft = 7 12 = xlInsideHorizontal am I correct?

 

[Rick Rothstein]

' Values of xlConstants for borders
'xlDiagonalDown = 5
'xlDiagonalUp = 6
'xlEdgeLeft = 7
'xlEdgeTop = 8
'xlEdgeBottom = 9
'xlEdgeRight = 10
'xlInsideVertical = 11
'xlInsideHorizontal = 12

For i = 7 To 12
With Range("$A$4:$H$36").Borders(i)
.LineStyle = xlContinuous
.Color = RGB(0, 0, 255)
End With
Next i

That code ends up doing all borders... the OP's original code did not do the left, top and right outside borders for the full range's outline. However, the OP seems to have indicated the outcome from your code does what he wants. If that is the case, then your code could be replaced with this...

Range("$A$4:$H$36").Borders.LineStyle = xlContinuous
Range("$A$4:$H$36").Borders.Color = RGB(0, 0, 255)

 

[AlphaFrog]

I think I got it from your post above. 7 to 12 is the line number style that corresponds to the style.
xlEdgeLeft = 7 12 = xlInsideHorizontal am I correct?

Yes

 

[JBShandrew]

That code ends up doing all borders... the OP's original code did not do the left, top and right outside borders for the full range's outline. However, the OP seems to have indicated the outcome from your code does what he wants. If that is the case, then your code could be replaced with this...

Range("$A$4:$H$36").Borders.LineStyle = xlContinuous
Range("$A$4:$H$36").Borders.Color = RGB(0, 0, 255)

Thank for for the quick responce. I am new to VBA. Can you please tell me what 7 to 12 means? Is that the number of border types xl_ _ _ ?

I think I got it from your post above. 7 to 12 is the line number style that corresponds to the style.
xlEdgeLeft = 7 12 = xlInsideHorizontal am I correct?

Thank for the Explanation Mr. Rothstein. It is good to know that there is more than one way to make VBA do what I want it to do.