+ Reply to Thread
Results 1 to 5 of 5

VBA Issues when selecting cell range

Hybrid View

  1. #1
    Registered User
    Join Date
    09-28-2011
    Location
    Windsor
    MS-Off Ver
    Excel 2007
    Posts
    15

    VBA Issues when selecting cell range

    I use a spreadsheet that needs the borders changed on a daily basis. The following code is designed to reset a particular range of cells to a basic border as shown below. The issue I am having is with my range selection. It gives me the error code 1004 - Method 'Range' of object '_Global' failed. I can't seem to figure out what the problem is and I was hoping someone could help. Thanks

    Sub Grid()
    
        Range( _
            "C4:ED6,C7:ED9,C10:ED12,C13:ED15,C16:ED18,C19:ED21,C22:ED24,C25:ED27,C28:ED30,C31:ED33,C34:ED36,C37:ED39,C40:ED42,C43:ED45,C46:ED48,C49:ED51,C52:ED54,C55:ED57,C58:ED60,C61:ED63,C64:ED66,C67:ED69,C70:ED72,C73:ED75,C76:ED78,C79:ED81,C82:ED84,C85:ED87,C88:ED90,C91:ED93,C94:ED96,C97:ED99,C100:ED102,C103:ED105,C106:ED108,C109:ED111,C112:ED114,C115:ED117,C118:ED120,C121:ED123,C124:ED126,C127:ED129,C130:ED132,C133:ED135,C136:ED138,C139:ED141" _
            ).Select
        
            
        
            
            
            
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End Sub

  2. #2
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: VBA Issues when selecting cell range

    try somthing like this
    Sub Grid()
    Dim myrange As Range
        Set myrange = Union(Range("C4:ED6"), Range("C7:ED9"), Range("C10:ED12"))
        
            myrange.Select
            
            
            
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End Sub
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  3. #3
    Registered User
    Join Date
    09-28-2011
    Location
    Windsor
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: VBA Issues when selecting cell range

    I edited the code as follows and the following error came up...

    Compile error:

    Wrong number of arguments or invalid property assignment.

    Sub Grid()
    
        Dim myrange As Range
        
        Set myrange = Union(Range("C4:ED6"), Range("C7:ED9"), Range("C10:ED12"), _
        Range("C13:ED15"), Range("C16:ED18"), Range("C19:ED21"), Range("C22:ED24"), _
        Range("C25:ED27"), Range("C28:ED30"), Range("C31:ED33"), Range("C34:ED36"), _
        Range("C37:ED39"), Range("C40:ED42"), Range("C43:ED45"), Range("C46:ED48"), _
        Range("C49:ED51"), Range("C52:ED54"), Range("C55:ED57"), Range("C58:ED60"), _
        Range("C61:ED63"), Range("C64:ED66"), Range("C67:ED69"), Range("C70:ED72"), _
        Range("C73:ED75"), Range("C76:ED78"), Range("C79:ED81"), Range("C82:ED84"), _
        Range("C85:ED87"), Range("C88:ED90"), Range("C91:ED93"), Range("C94:ED96"), _
        Range("C97:ED99"), Range("C100:ED102"), Range("C103:ED105"), Range("C106:ED108"), _
        Range("C109:ED111"), Range("C112:ED114"), Range("C115:ED117"), Range("C118:ED120"), _
        Range("C121:ED123"), Range("C124:ED126"), Range("C127:ED129"), Range("C130:ED132"), _
        Range("C133:ED135"), Range("C136:ED138"), Range("C139:ED141"))
            
            myrange.Select
    
        
            
        
            
            
            
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        Selection.Borders(xlEdgeTop).LineStyle = xlNone
        Selection.Borders(xlEdgeBottom).LineStyle = xlNone
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
        
        With Selection.Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ThemeColor = 2
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End Sub

  4. #4
    Forum Expert Whizbang's Avatar
    Join Date
    08-05-2009
    Location
    Greenville, NH
    MS-Off Ver
    2010
    Posts
    1,395

    Re: VBA Issues when selecting cell range

    Sub Grid()
    
        For i = 1 To 46
    
            With Range("C" & (i * 3) + 1 & ":ED" & (i * 3) + 3)
                .Borders(xlDiagonalDown).LineStyle = xlNone
                .Borders(xlDiagonalUp).LineStyle = xlNone
                .Borders(xlEdgeLeft).LineStyle = xlNone
                .Borders(xlEdgeTop).LineStyle = xlNone
                .Borders(xlEdgeBottom).LineStyle = xlNone
                .Borders(xlEdgeRight).LineStyle = xlNone
                .Borders(xlInsideVertical).LineStyle = xlNone
                .Borders(xlInsideHorizontal).LineStyle = xlNone
                
                With .Borders(xlEdgeLeft)
                    .LineStyle = xlContinuous
                    .ThemeColor = 2
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeTop)
                    .LineStyle = xlContinuous
                    .ThemeColor = 2
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeBottom)
                    .LineStyle = xlContinuous
                    .ThemeColor = 2
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlEdgeRight)
                    .LineStyle = xlContinuous
                    .ThemeColor = 2
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
                With .Borders(xlInsideVertical)
                    .LineStyle = xlContinuous
                    .ThemeColor = 2
                    .TintAndShade = 0
                    .Weight = xlThin
                End With
            End With
        Next i
    End Sub

  5. #5
    Registered User
    Join Date
    09-28-2011
    Location
    Windsor
    MS-Off Ver
    Excel 2007
    Posts
    15

    Re: VBA Issues when selecting cell range

    Thanks. Your code worked.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1