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