Find text in a cell and select whole row question

[crowdx42]

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

[[email protected]]

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

[crowdx42]

That worked perfect, just changed a few parameters and it was perfect.
Thanks
Patrick

[crowdx42]

Just as a followup here, I have added the rest of the page formating code to this and the two parts I am having problems with is I want to delete the last 4 characters in the sheet name ".xls" and then I want to enter this in as a name at the top of the sheet. Finally I want to put a line in the same format as the original code across the bottom of the last cell I have data in.
What I have so far is list below ( a little messy code but it is working LOL )
Thanks for all the help
Patrick

Sub Format_Page()
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(xlEdgeTop)
.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(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End If
Set FindNext2 = FindNext
FindNext2.Interior.ColorIndex = 0
FindNext2.Select
Loop Until FindNext.Address = Findfirst.Address
End If
'tidy them up:
Set Findfirst = Nothing
Set FindNext = Nothing
Set FindNext2 = Nothing
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("A6").Select
ActiveCell.FormulaR1C1 = "BANKER"
With ActiveCell.Characters(Start:=1, Length:=6).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("B6").Select
ActiveCell.FormulaR1C1 = "PRODUCT"
With ActiveCell.Characters(Start:=1, Length:=7).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("C6").Select
Rows("6:6").RowHeight = 30.75
Range("F6").Select
ActiveCell.FormulaR1C1 = ""
Range("A6:F6").Select
With Selection.Interior
.ColorIndex = 37
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("C8").Select
Columns("C:C").ColumnWidth = 11
Columns("D:D").ColumnWidth = 10.29
Columns("E:E").ColumnWidth = 7.43
Columns("C:E").Select
Range("C2").Activate
With Selection
.HorizontalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Columns("F:F").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(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Range("A4:E4").Select
Range("E4").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Rows("4:4").RowHeight = 25.5
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 2
End With
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Range("A5:E5").Select
Range("C5").Activate
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Rows("5:5").RowHeight = 23.25
With Selection.Interior
.ColorIndex = 1
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
Selection.Font.ColorIndex = 2
Rows("1:3").Select
Range("A3").Activate
Selection.Delete Shift:=xlUp
Range("A1:E1").Select
End Sub