Results 1 to 7 of 7

borders between 2 specific words

Threaded View

  1. #1
    Registered User
    Join Date
    07-02-2009
    Location
    montreal
    MS-Off Ver
    Excel 2003
    Posts
    13

    borders between 2 specific words

    Hi all,

    I have this bit of code that I need help with:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    
    Dim lRow As Long
    Dim LR1 As Long
    Dim LR2 As Long
    Dim LC As Long
        
        With ActiveSheet.Cells
        .Interior.ColorIndex = xlNone
        .Borders.LineStyle = xlNone
            
     On Error GoTo skip
     
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
     
    LR1 = .Find("word1", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row + 1
        LR2 = .Find("word2", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious, False, False).Row - 1
        LC = .Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByColumns, xlPrevious, False, False).Column
    
        End With
    
    lRow = 0
    
        For lRow = LR1 To LR2 Step 1   'LR1 is "word1" - "LR2 is "word2"
            With Range(Cells(lRow, 1), Cells(lRow, LC))
            .Interior.ColorIndex = 24
                With .Borders
                    For i = 7 To 11
                        With .Item(i)
                        .LineStyle = xlDot
                        '.Weight = xlThin
                        .ColorIndex = xlAutomatic
                        End With
                    Next i
                End With
            End With
        Next lRow
          
    skip:
        If Err Then
        MsgBox Err.Description, vbCritical, "ERROR"
        End If
    
    On Error GoTo 0
    
    Application.ScreenUpdating = True
    
    End Sub
    I am trying to is create borders around all cells in the range by looking for 2 specific words to begin and end the borders. Let's say word1 & word2. The border should begin after (under) word1 and end before (above) word2. It will also happen that there will be several occurrences of word1 & word 2 but it should only create the borders between word1 & word 2 for every occurrence of those words.

    Now this code seems to works but only for 1 occurrence of word1 & word2 (from the bottom up) and all the others are ignored

    Can someone help please ?

    Pedy
    Last edited by pedy; 07-08-2009 at 11:06 AM.

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