hi,

Try this one :

Option Explicit
Sub findsomething()
Dim Findfirst As Object, FindNext As Object, FindNext2 As Object
Set Findfirst = Cells.Find(What:="ATM CARDS", LookIn:=xlValues)
If Not Findfirst Is Nothing Then
Findfirst.Select
With Range("A" & Findfirst.Row & ":F" &
Findfirst.Row).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Set FindNext2 = Findfirst
Do
Set FindNext = Cells.FindNext(After:=FindNext2)
If Not FindNext Is Nothing Then
With Range("A" & FindNext.Row & ":F" &
FindNext.Row).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If
Set FindNext2 = FindNext
FindNext2.Interior.ColorIndex = 3
FindNext2.Select
Loop Until FindNext.Address = Findfirst.Address
End If
'tidy them up:
Set Findfirst = Nothing
Set FindNext = Nothing
Set FindNext2 = Nothing
End Sub

Rgds,

halim


crowdx42 menuliskan:
Ok,
so what I am trying to do is find some text in a cell (I know the text
represents the end of a list in a row of text) and then select the row
from A:F and then format the selected cells with a line on the bottom.
I have gotten it to run when recording a macro BUT when I apply to
other sheets it does not format the way it should.
This is the initial starting code but I want to loop this with the row
range also added.
All help appreciated
Patrick

Sub Sheet_Format()
Cells.Find(What:="ATM CARDS", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows("7:7").Select
Range("B7").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.FindNext(After:=ActiveCell).Activate
Selection.FindNext(After:=ActiveCell).Activate
Range("B7").Select
Cells.FindNext(After:=ActiveCell).Activate
Range("A8").Select
End Sub


--
crowdx42
------------------------------------------------------------------------
crowdx42's Profile: http://www.excelforum.com/member.php...o&userid=37749
View this thread: http://www.excelforum.com/showthread...hreadid=573340