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

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

That worked perfect, just changed a few parameters and it was perfect.
Thanks :)
Patric

--
crowdx4
-----------------------------------------------------------------------
crowdx42's Profile: http://www.excelforum.com/member.php...fo&userid=3774
View this thread: http://www.excelforum.com/showthread.php?threadid=57334

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


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