Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Changing Borders In A Document With Vba

  1. #1
    Join Date
    28th March 2007
    Posts
    3

    Changing Borders In A Document With Vba

    Hello,

    I have a report that is imported into excel with all thick borders. Does anyone know a VBA code (or simpler way) to change all of these borders to skinny borders? Thanks.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,377

    Re: Changing Borders In A Document With Vba

    something like this should help you

    Code:
    Sub formatCurrentRegion()
    
        Selection.CurrentRegion.Select
    
        With Selection
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeLeft).Weight = xlThin
            .Borders(xlEdgeLeft).ColorIndex = xlAutomatic
            .Borders(xlEdgeTop).LineStyle = xlContinuous
            .Borders(xlEdgeTop).Weight = xlThin
            .Borders(xlEdgeTop).ColorIndex = xlAutomatic
            .Borders(xlEdgeBottom).LineStyle = xlContinuous
            .Borders(xlEdgeBottom).Weight = xlThin
            .Borders(xlEdgeBottom).ColorIndex = xlAutomatic
            .Borders(xlEdgeRight).LineStyle = xlContinuous
            .Borders(xlEdgeRight).Weight = xlThin
            .Borders(xlEdgeRight).ColorIndex = xlAutomatic
            .Borders(xlInsideVertical).LineStyle = xlContinuous
            .Borders(xlInsideVertical).Weight = xlThin
            .Borders(xlInsideVertical).ColorIndex = xlAutomatic
            .Borders(xlInsideHorizontal).LineStyle = xlContinuous
            .Borders(xlInsideHorizontal).Weight = xlThin
            .Borders(xlInsideHorizontal).ColorIndex = xlAutomatic
        End With
        
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,324

    Re: Changing Borders In A Document With Vba

    Or this:
    Code:
    Sub x()
            
        Dim rCel As Range
        Dim iEdge As Integer
        
        Application.ScreenUpdating = False
        For Each rCel In ActiveSheet.UsedRange
            With rCel
                For iEdge = xlDiagonalDown To xlEdgeRight
                    If .Borders(iEdge).LineStyle <> xlLineStyleNone Then
                        .Borders(iEdge).LineStyle = xlContinuous
                        .Borders(iEdge).Weight = xlHairline
                    End If
                Next
            End With
        Next
        Application.ScreenUpdating = True
    
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    28th March 2007
    Posts
    3

    Re: Changing Borders In A Document With Vba

    Thanks! This worked perfectly.

    Quote Originally Posted by shg
    Or this:
    Code:
    Sub x()
            
        Dim rCel As Range
        Dim iEdge As Integer
        
        Application.ScreenUpdating = False
        For Each rCel In ActiveSheet.UsedRange
            With rCel
                For iEdge = xlDiagonalDown To xlEdgeRight
                    If .Borders(iEdge).LineStyle <> xlLineStyleNone Then
                        .Borders(iEdge).LineStyle = xlContinuous
                        .Borders(iEdge).Weight = xlHairline
                    End If
                Next
            End With
        Next
        Application.ScreenUpdating = True
    
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,324

    Re: Changing Borders In A Document With Vba

    Great, you're welcome.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. changing document name for printing
    By Zeno_nz in forum EXCEL HELP
    Replies: 2
    Last Post: February 23rd, 2005, 15:30

Bookmarks

Posting Permissions

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