Excel VBA question

[gjsala]

I'm trying to put a border in a group a cells. The rows are dynamic but the last cell in column one always ends with the word "Stop". I would like to put borders in cells located in columns "A" through "H".

Thanks in advanced!

 

[PHV]

And what have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gjsala]

So far I have this but I'm thinking this is going down the wrong path:
For r = 1 To numRow
If Cells(r, 1) = strStop Then
Cells(r, 1).Select
Cells(r, 2).Select
Cells(r, 3).Select
Cells(r, 4).Select
Cells(r, 5).Select
Cells(r, 6).Select
Cells(r, 7).Select
End If
Next r

 

[PHV]

The macro recorder is your best friend for getting started.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gjsala]

The problem with the macro recorder is there's a defined range (A1:B7). The problem lies that the rows I encounter are going to change in every sheet. This is the part I'm not sure about.

 

[PHV]

How your posted code is expected to put borders on cells ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[firefytr]

How about a seperate function with parameters...

Sub test()

    Dim ws As Worksheet, rngLook As Range, LastRow As Long
    Set ws = Sheets("Sheet1")
    Set rngLook = ws.Range("H:H")
    On Error Resume Next 'if 'Stop' not found
    LastRow = rngLook.Find("Stop", after:=rngLook(1), searchorder:=xlByRows, searchdirection:=xlPrevious).Row
    If LastRow <> 0 Then
        FormatRange ws.Range("A1:H" & LastRow), "none" '"around"
    End If

End Sub

Sub FormatRange(rngRef As Range, BorderType As String)
    If LCase(BorderType) = "none" Then
        rngRef.Borders(xlInsideVertical).LineStyle = xlNone
        rngRef.Borders(xlInsideHorizontal).LineStyle = xlNone
        rngRef.Borders(xlDiagonalDown).LineStyle = xlNone
        rngRef.Borders(xlDiagonalUp).LineStyle = xlNone
        rngRef.Borders(xlEdgeBottom).LineStyle = xlNone
        rngRef.Borders(xlEdgeLeft).LineStyle = xlNone
        rngRef.Borders(xlEdgeRight).LineStyle = xlNone
        rngRef.Borders(xlEdgeTop).LineStyle = xlNone
    ElseIf LCase(BorderType) = "around" Then
        rngRef.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic
    End If
End Sub

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci