Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell Borders (XP)

    Is there a better way to code a cell border's outline other than using these lines of code?

    With Selection.Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    .ColorIndex = xlAutomatic
    End With

    With Selection.Borders(xlEdgeTop)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    .ColorIndex = xlAutomatic
    End With

    With Selection.Borders(xlEdgeBottom)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    .ColorIndex = xlAutomatic
    End With

    With Selection.Borders(xlEdgeRight)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    .ColorIndex = xlAutomatic
    End With

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Borders (XP)

    You could shorten it a bit:

    Selection.BorderAround LineStyle:=xlContinuous, Weight:=xlHairline, ColorIndex:=xlColorIndexAutomatic

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Cell Borders (XP)

    Depends what kind of borders you want. If you want just outline, then that's the best way.

    Edit: or you could use Hans' version!
    If you want borders around every cell in the selection, you could use:
    <pre>With Selection.Borders
    .LineStyle = xlContinuous
    .Weight = xlHairline
    .ColorIndex = xlAutomatic
    End With
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Borders (XP)

    Yet another version:

    Dim intBorder As Integer
    For intBorder = xlEdgeLeft To xlEdgeRight
    With Selection.Borders(intBorder)
    .LineStyle = xlContinuous
    .Weight = xlHairline
    .ColorIndex = xlColorIndexAutomatic
    End With
    Next intBorder

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Borders (XP)

    Hans,

    It worked. Just one more thing.

    Within this line of code how does on change the Interior ColorIndex say to "19"?

    I would have thought it would be something similar to: ColorIndex:=xlInteriorColor = 19

    The code will not compile because of the two equal signs.

    Thanks,
    John

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Cell Borders (XP)

    Just <code>.Colorindex = 19</code>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Borders (XP)

    Rory,

    I tried that prior to posting. The interior color should be a light yellow. This is not the case with ColorIndex:=19 as the interior is still white while the outer edges of the cell are white. The hariline outline is not visible because of the white outer edges.

    Regards,
    John

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Cell Borders (XP)

    Something like:

    <pre>Selection.Interior.ColorIndex = 19</pre>


    Steve

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Borders (XP)

    Steve,

    I would like to include it in Han's suggestion:

    Selection.BorderAround LineStyle:=xlContinuous, Weight:=xlHairline, ColorIndex:=xlColorIndexAutomatic

    Regards,
    John

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cell Borders (XP)

    You cannot do that in one instruction. You must use separate instructions for the borders and for the interior.

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell Borders (XP)

    That is what I had thought.

    Thank you for the confirmation.
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •