Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 3
Default borders between 2 specific words

Hi all,

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

Code:
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't print only borders. I want a grid sheet no words rs metz Excel Discussion (Misc queries) 4 August 10th 07 11:12 PM
Searching for the presence of specific words Ryan Ferrell Excel Worksheet Functions 1 December 25th 05 10:34 PM
Search for specific words Newberry Excel Worksheet Functions 3 July 21st 05 10:18 PM
counting specific words jen Excel Worksheet Functions 2 June 25th 05 12:46 AM
Highlight a row if a specific cell is specific numbers/words sea0221 Excel Worksheet Functions 2 March 9th 05 12:06 AM


All times are GMT +1. The time now is 04:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"