I am relatively new to recording macros with excel 2003. The issue I have is
that I have the macro recorded and working properly. This macro will be used
frequently in the same worksheet. It is basic formatting, text and formulas.
What I would like to have happen is that when I run the maro it wll drop down
vertically within the same columns from the range of cells above. Basically
so the macro will cascade down vertically. Does this make sense. Can anyone
help me. Thanks.
As ALWAYS, post your coding efforts (no attachments) for comments.
--
Don Guillett
SalesAid Software
quot;Joequot; gt; wrote in message
...
gt;I am relatively new to recording macros with excel 2003. The issue I have
gt;is
gt; that I have the macro recorded and working properly. This macro will be
gt; used
gt; frequently in the same worksheet. It is basic formatting, text and
gt; formulas.
gt; What I would like to have happen is that when I run the maro it wll drop
gt; down
gt; vertically within the same columns from the range of cells above.
gt; Basically
gt; so the macro will cascade down vertically. Does this make sense. Can
gt; anyone
gt; help me. Thanks.
Do you mean that you want the macro to run repeatedly with each cell in the
column, in turn, being the active cell?. If not, try to explain it again,
and, as Don said, include your current code in your post. HTH Otto
quot;Joequot; gt; wrote in message
...
gt;I am relatively new to recording macros with excel 2003. The issue I have
gt;is
gt; that I have the macro recorded and working properly. This macro will be
gt; used
gt; frequently in the same worksheet. It is basic formatting, text and
gt; formulas.
gt; What I would like to have happen is that when I run the maro it wll drop
gt; down
gt; vertically within the same columns from the range of cells above.
gt; Basically
gt; so the macro will cascade down vertically. Does this make sense. Can
gt; anyone
gt; help me. Thanks.
Here is the VBA code. What I would like to have happen is everytime I run
this macro is to have it format the cells directly underneath the previously
formatted cells, So it just keeps going down the spreadsheet. For example
this code will format cells in the range of G1 to M 20, the next time I run
the macro it should format cells G21 to M40. Does this make sense?
Sub NEWSHT()
'
' NEWSHT Macro
' Macro recorded 5/7/2006 by
'
'
Rows(quot;1:1quot;).Select
Selection.RowHeight = 20
Range(quot;G1quot;).Select
ActiveCell.FormulaR1C1 = quot;Remarksquot;
Range(quot;H1quot;).Select
ActiveCell.FormulaR1C1 = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
With ActiveCell.Characters(Start:=1, Length:=18).Font
.Name = quot;Arialquot;
.FontStyle = quot;Regularquot;
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range(quot;I1quot;).Select
ActiveCell.FormulaR1C1 = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
With ActiveCell.Characters(Start:=1, Length:=19).Font
.Name = quot;Arialquot;
.FontStyle = quot;Regularquot;
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range(quot;J1quot;).Select
ActiveCell.FormulaR1C1 = quot;Diff.quot;
Range(quot;K1quot;).Select
ActiveCell.FormulaR1C1 = quot;Fillquot;
Range(quot;L1quot;).Select
ActiveCell.FormulaR1C1 = quot;Cutquot;
Range(quot;M1quot;).Select
ActiveCell.FormulaR1C1 = quot;Descriptionquot;
Columns(quot;M:Mquot;).Select
Selection.ColumnWidth = 20
Columns(quot;G:Gquot;).Select
Selection.ColumnWidth = 20
Columns(quot;H:Hquot;).Select
Selection.ColumnWidth = 12
Columns(quot;I:Iquot;).Select
Selection.ColumnWidth = 12
ActiveWindow.SmallScroll Down:=-3
Range(quot;G1:M20quot;).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range(quot;G1:M1quot;).Select
ActiveWindow.ScrollColumn = 3
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range(quot;J22quot;).Select
ActiveWindow.SmallScroll Down:=-12
Rows(quot;1:1quot;).EntireRow.AutoFit
Range(quot;G1:M1quot;).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range(quot;G1quot;).Select
End Sub
quot;Joequot; wrote:
gt; I am relatively new to recording macros with excel 2003. The issue I have is
gt; that I have the macro recorded and working properly. This macro will be used
gt; frequently in the same worksheet. It is basic formatting, text and formulas.
gt; What I would like to have happen is that when I run the maro it wll drop down
gt; vertically within the same columns from the range of cells above. Basically
gt; so the macro will cascade down vertically. Does this make sense. Can anyone
gt; help me. Thanks.
try this instead of your code. NO selections
Sub trythisinstead()
nr = Range(quot;a1quot;).SpecialCells(xlLastCell).Row
'MsgBox nr
Rows(nr).RowHeight = 20
Cells(nr, quot;gquot;) = quot;Remarksquot;
Cells(nr, quot;Hquot;) = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
Cells(nr, quot;Iquot;) = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
Cells(nr, quot;Jquot;) = quot;Diff.quot;
Cells(nr, quot;Kquot;) = quot;Fillquot;
Cells(nr, quot;Lquot;) = quot;Cutquot;
Cells(nr, quot;Mquot;) = quot;Descriptionquot;
Columns(quot;gquot;).ColumnWidth = 20
Columns(quot;Mquot;).ColumnWidth = 20
Columns(quot;H:iquot;).ColumnWidth = 12
Range(Cells(nr, quot;gquot;), Cells(nr 19, quot;mquot;)) _
..BorderAround LineStyle:=xlContinuous, Weight:=xlThick
With Range(Cells(nr, quot;gquot;), Cells(nr, quot;mquot;))
.BorderAround LineStyle:=xlContinuous, Weight:=xlThick
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With
Rows(nr).EntireRow.AutoFit
End Sub
--
Don Guillett
SalesAid Software
quot;Joequot; gt; wrote in message
...
gt; Here is the VBA code. What I would like to have happen is everytime I run
gt; this macro is to have it format the cells directly underneath the
gt; previously
gt; formatted cells, So it just keeps going down the spreadsheet. For example
gt; this code will format cells in the range of G1 to M 20, the next time I
gt; run
gt; the macro it should format cells G21 to M40. Does this make sense?
gt;
gt; Sub NEWSHT()
gt; '
gt; ' NEWSHT Macro
gt; ' Macro recorded 5/7/2006 by
gt; '
gt;
gt; '
gt; Rows(quot;1:1quot;).Select
gt; Selection.RowHeight = 20
gt; Range(quot;G1quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;Remarksquot;
gt; Range(quot;H1quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt; With ActiveCell.Characters(Start:=1, Length:=18).Font
gt; .Name = quot;Arialquot;
gt; .FontStyle = quot;Regularquot;
gt; .Size = 10
gt; .Strikethrough = False
gt; .Superscript = False
gt; .Subscript = False
gt; .OutlineFont = False
gt; .Shadow = False
gt; .Underline = xlUnderlineStyleNone
gt; .ColorIndex = xlAutomatic
gt; End With
gt; Range(quot;I1quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt; With ActiveCell.Characters(Start:=1, Length:=19).Font
gt; .Name = quot;Arialquot;
gt; .FontStyle = quot;Regularquot;
gt; .Size = 10
gt; .Strikethrough = False
gt; .Superscript = False
gt; .Subscript = False
gt; .OutlineFont = False
gt; .Shadow = False
gt; .Underline = xlUnderlineStyleNone
gt; .ColorIndex = xlAutomatic
gt; End With
gt; Range(quot;J1quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;Diff.quot;
gt; Range(quot;K1quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;Fillquot;
gt; Range(quot;L1quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;Cutquot;
gt; Range(quot;M1quot;).Select
gt; ActiveCell.FormulaR1C1 = quot;Descriptionquot;
gt; Columns(quot;M:Mquot;).Select
gt; Selection.ColumnWidth = 20
gt; Columns(quot;G:Gquot;).Select
gt; Selection.ColumnWidth = 20
gt; Columns(quot;H:Hquot;).Select
gt; Selection.ColumnWidth = 12
gt; Columns(quot;I:Iquot;).Select
gt; Selection.ColumnWidth = 12
gt; ActiveWindow.SmallScroll Down:=-3
gt; Range(quot;G1:M20quot;).Select
gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt; With Selection.Borders(xlEdgeLeft)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThick
gt; .ColorIndex = xlAutomatic
gt; End With
gt; With Selection.Borders(xlEdgeTop)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThick
gt; .ColorIndex = xlAutomatic
gt; End With
gt; With Selection.Borders(xlEdgeBottom)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThick
gt; .ColorIndex = xlAutomatic
gt; End With
gt; With Selection.Borders(xlEdgeRight)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThick
gt; .ColorIndex = xlAutomatic
gt; End With
gt; With Selection.Borders(xlInsideVertical)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = xlAutomatic
gt; End With
gt; With Selection.Borders(xlInsideHorizontal)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = xlAutomatic
gt; End With
gt; Range(quot;G1:M1quot;).Select
gt; ActiveWindow.ScrollColumn = 3
gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt; With Selection.Borders(xlEdgeLeft)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThick
gt; .ColorIndex = xlAutomatic
gt; End With
gt; With Selection.Borders(xlEdgeTop)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThick
gt; .ColorIndex = xlAutomatic
gt; End With
gt; With Selection.Borders(xlEdgeBottom)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThick
gt; .ColorIndex = xlAutomatic
gt; End With
gt; With Selection.Borders(xlEdgeRight)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThick
gt; .ColorIndex = xlAutomatic
gt; End With
gt; With Selection.Borders(xlInsideVertical)
gt; .LineStyle = xlContinuous
gt; .Weight = xlThin
gt; .ColorIndex = xlAutomatic
gt; End With
gt; Range(quot;J22quot;).Select
gt; ActiveWindow.SmallScroll Down:=-12
gt; Rows(quot;1:1quot;).EntireRow.AutoFit
gt; Range(quot;G1:M1quot;).Select
gt; With Selection
gt; .HorizontalAlignment = xlCenter
gt; .VerticalAlignment = xlBottom
gt; .Orientation = 0
gt; .AddIndent = False
gt; .ShrinkToFit = False
gt; .MergeCells = False
gt; End With
gt; Range(quot;G1quot;).Select
gt; End Sub
gt;
gt;
gt;
gt; quot;Joequot; wrote:
gt;
gt;gt; I am relatively new to recording macros with excel 2003. The issue I have
gt;gt; is
gt;gt; that I have the macro recorded and working properly. This macro will be
gt;gt; used
gt;gt; frequently in the same worksheet. It is basic formatting, text and
gt;gt; formulas.
gt;gt; What I would like to have happen is that when I run the maro it wll drop
gt;gt; down
gt;gt; vertically within the same columns from the range of cells above.
gt;gt; Basically
gt;gt; so the macro will cascade down vertically. Does this make sense. Can
gt;gt; anyone
gt;gt; help me. Thanks.
Don
Your code puts a border around the entire range G1:M20 as well as G1:M1,
but no borders around each cell in the range. The OP's code puts borders
around each cell so I assume that's what he wanted. Is there a code that
puts borders around each cell in a range without using the laborious:
Borders(xlEdgeLeft)
Borders(xlEdgeRight)
Borders(xlEdgeTop)
Borders(xlEdgeBottom)
and all the attending lines of code that goes with each of these lines of
code (such as what you get when you record a macro and specify each edge)?
Thanks for your help. Otto
quot;Don Guillettquot; gt; wrote in message
...
gt; try this instead of your code. NO selections
gt;
gt; Sub trythisinstead()
gt; nr = Range(quot;a1quot;).SpecialCells(xlLastCell).Row
gt; 'MsgBox nr
gt; Rows(nr).RowHeight = 20
gt; Cells(nr, quot;gquot;) = quot;Remarksquot;
gt; Cells(nr, quot;Hquot;) = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt; Cells(nr, quot;Iquot;) = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt; Cells(nr, quot;Jquot;) = quot;Diff.quot;
gt; Cells(nr, quot;Kquot;) = quot;Fillquot;
gt; Cells(nr, quot;Lquot;) = quot;Cutquot;
gt; Cells(nr, quot;Mquot;) = quot;Descriptionquot;
gt; Columns(quot;gquot;).ColumnWidth = 20
gt; Columns(quot;Mquot;).ColumnWidth = 20
gt; Columns(quot;H:iquot;).ColumnWidth = 12
gt;
gt; Range(Cells(nr, quot;gquot;), Cells(nr 19, quot;mquot;)) _
gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt;
gt; With Range(Cells(nr, quot;gquot;), Cells(nr, quot;mquot;))
gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt; .HorizontalAlignment = xlCenter
gt; .VerticalAlignment = xlBottom
gt; End With
gt;
gt; Rows(nr).EntireRow.AutoFit
gt;
gt; End Sub
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Joequot; gt; wrote in message
gt; ...
gt;gt; Here is the VBA code. What I would like to have happen is everytime I run
gt;gt; this macro is to have it format the cells directly underneath the
gt;gt; previously
gt;gt; formatted cells, So it just keeps going down the spreadsheet. For example
gt;gt; this code will format cells in the range of G1 to M 20, the next time I
gt;gt; run
gt;gt; the macro it should format cells G21 to M40. Does this make sense?
gt;gt;
gt;gt; Sub NEWSHT()
gt;gt; '
gt;gt; ' NEWSHT Macro
gt;gt; ' Macro recorded 5/7/2006 by
gt;gt; '
gt;gt;
gt;gt; '
gt;gt; Rows(quot;1:1quot;).Select
gt;gt; Selection.RowHeight = 20
gt;gt; Range(quot;G1quot;).Select
gt;gt; ActiveCell.FormulaR1C1 = quot;Remarksquot;
gt;gt; Range(quot;H1quot;).Select
gt;gt; ActiveCell.FormulaR1C1 = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt; With ActiveCell.Characters(Start:=1, Length:=18).Font
gt;gt; .Name = quot;Arialquot;
gt;gt; .FontStyle = quot;Regularquot;
gt;gt; .Size = 10
gt;gt; .Strikethrough = False
gt;gt; .Superscript = False
gt;gt; .Subscript = False
gt;gt; .OutlineFont = False
gt;gt; .Shadow = False
gt;gt; .Underline = xlUnderlineStyleNone
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; Range(quot;I1quot;).Select
gt;gt; ActiveCell.FormulaR1C1 = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt; With ActiveCell.Characters(Start:=1, Length:=19).Font
gt;gt; .Name = quot;Arialquot;
gt;gt; .FontStyle = quot;Regularquot;
gt;gt; .Size = 10
gt;gt; .Strikethrough = False
gt;gt; .Superscript = False
gt;gt; .Subscript = False
gt;gt; .OutlineFont = False
gt;gt; .Shadow = False
gt;gt; .Underline = xlUnderlineStyleNone
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; Range(quot;J1quot;).Select
gt;gt; ActiveCell.FormulaR1C1 = quot;Diff.quot;
gt;gt; Range(quot;K1quot;).Select
gt;gt; ActiveCell.FormulaR1C1 = quot;Fillquot;
gt;gt; Range(quot;L1quot;).Select
gt;gt; ActiveCell.FormulaR1C1 = quot;Cutquot;
gt;gt; Range(quot;M1quot;).Select
gt;gt; ActiveCell.FormulaR1C1 = quot;Descriptionquot;
gt;gt; Columns(quot;M:Mquot;).Select
gt;gt; Selection.ColumnWidth = 20
gt;gt; Columns(quot;G:Gquot;).Select
gt;gt; Selection.ColumnWidth = 20
gt;gt; Columns(quot;H:Hquot;).Select
gt;gt; Selection.ColumnWidth = 12
gt;gt; Columns(quot;I:Iquot;).Select
gt;gt; Selection.ColumnWidth = 12
gt;gt; ActiveWindow.SmallScroll Down:=-3
gt;gt; Range(quot;G1:M20quot;).Select
gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThick
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThick
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThick
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThick
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThin
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; With Selection.Borders(xlInsideHorizontal)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThin
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; Range(quot;G1:M1quot;).Select
gt;gt; ActiveWindow.ScrollColumn = 3
gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThick
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThick
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThick
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThick
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt; .LineStyle = xlContinuous
gt;gt; .Weight = xlThin
gt;gt; .ColorIndex = xlAutomatic
gt;gt; End With
gt;gt; Range(quot;J22quot;).Select
gt;gt; ActiveWindow.SmallScroll Down:=-12
gt;gt; Rows(quot;1:1quot;).EntireRow.AutoFit
gt;gt; Range(quot;G1:M1quot;).Select
gt;gt; With Selection
gt;gt; .HorizontalAlignment = xlCenter
gt;gt; .VerticalAlignment = xlBottom
gt;gt; .Orientation = 0
gt;gt; .AddIndent = False
gt;gt; .ShrinkToFit = False
gt;gt; .MergeCells = False
gt;gt; End With
gt;gt; Range(quot;G1quot;).Select
gt;gt; End Sub
gt;gt;
gt;gt;
gt;gt;
gt;gt; quot;Joequot; wrote:
gt;gt;
gt;gt;gt; I am relatively new to recording macros with excel 2003. The issue I
gt;gt;gt; have is
gt;gt;gt; that I have the macro recorded and working properly. This macro will be
gt;gt;gt; used
gt;gt;gt; frequently in the same worksheet. It is basic formatting, text and
gt;gt;gt; formulas.
gt;gt;gt; What I would like to have happen is that when I run the maro it wll drop
gt;gt;gt; down
gt;gt;gt; vertically within the same columns from the range of cells above.
gt;gt;gt; Basically
gt;gt;gt; so the macro will cascade down vertically. Does this make sense. Can
gt;gt;gt; anyone
gt;gt;gt; help me. Thanks.
gt;
gt;
..borders instead of .borderaround
--
Don Guillett
SalesAid Software
quot;Otto Moehrbachquot; gt; wrote in message
...
gt; Don
gt; Your code puts a border around the entire range G1:M20 as well as
gt; G1:M1, but no borders around each cell in the range. The OP's code puts
gt; borders around each cell so I assume that's what he wanted. Is there a
gt; code that puts borders around each cell in a range without using the
gt; laborious:
gt; Borders(xlEdgeLeft)
gt; Borders(xlEdgeRight)
gt; Borders(xlEdgeTop)
gt; Borders(xlEdgeBottom)
gt; and all the attending lines of code that goes with each of these lines of
gt; code (such as what you get when you record a macro and specify each edge)?
gt; Thanks for your help. Otto
gt;
gt; quot;Don Guillettquot; gt; wrote in message
gt; ...
gt;gt; try this instead of your code. NO selections
gt;gt;
gt;gt; Sub trythisinstead()
gt;gt; nr = Range(quot;a1quot;).SpecialCells(xlLastCell).Row
gt;gt; 'MsgBox nr
gt;gt; Rows(nr).RowHeight = 20
gt;gt; Cells(nr, quot;gquot;) = quot;Remarksquot;
gt;gt; Cells(nr, quot;Hquot;) = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt; Cells(nr, quot;Iquot;) = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt; Cells(nr, quot;Jquot;) = quot;Diff.quot;
gt;gt; Cells(nr, quot;Kquot;) = quot;Fillquot;
gt;gt; Cells(nr, quot;Lquot;) = quot;Cutquot;
gt;gt; Cells(nr, quot;Mquot;) = quot;Descriptionquot;
gt;gt; Columns(quot;gquot;).ColumnWidth = 20
gt;gt; Columns(quot;Mquot;).ColumnWidth = 20
gt;gt; Columns(quot;H:iquot;).ColumnWidth = 12
gt;gt;
gt;gt; Range(Cells(nr, quot;gquot;), Cells(nr 19, quot;mquot;)) _
gt;gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt;gt;
gt;gt; With Range(Cells(nr, quot;gquot;), Cells(nr, quot;mquot;))
gt;gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt;gt; .HorizontalAlignment = xlCenter
gt;gt; .VerticalAlignment = xlBottom
gt;gt; End With
gt;gt;
gt;gt; Rows(nr).EntireRow.AutoFit
gt;gt;
gt;gt; End Sub
gt;gt;
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;Joequot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Here is the VBA code. What I would like to have happen is everytime I
gt;gt;gt; run
gt;gt;gt; this macro is to have it format the cells directly underneath the
gt;gt;gt; previously
gt;gt;gt; formatted cells, So it just keeps going down the spreadsheet. For
gt;gt;gt; example
gt;gt;gt; this code will format cells in the range of G1 to M 20, the next time I
gt;gt;gt; run
gt;gt;gt; the macro it should format cells G21 to M40. Does this make sense?
gt;gt;gt;
gt;gt;gt; Sub NEWSHT()
gt;gt;gt; '
gt;gt;gt; ' NEWSHT Macro
gt;gt;gt; ' Macro recorded 5/7/2006 by
gt;gt;gt; '
gt;gt;gt;
gt;gt;gt; '
gt;gt;gt; Rows(quot;1:1quot;).Select
gt;gt;gt; Selection.RowHeight = 20
gt;gt;gt; Range(quot;G1quot;).Select
gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Remarksquot;
gt;gt;gt; Range(quot;H1quot;).Select
gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt; With ActiveCell.Characters(Start:=1, Length:=18).Font
gt;gt;gt; .Name = quot;Arialquot;
gt;gt;gt; .FontStyle = quot;Regularquot;
gt;gt;gt; .Size = 10
gt;gt;gt; .Strikethrough = False
gt;gt;gt; .Superscript = False
gt;gt;gt; .Subscript = False
gt;gt;gt; .OutlineFont = False
gt;gt;gt; .Shadow = False
gt;gt;gt; .Underline = xlUnderlineStyleNone
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; Range(quot;I1quot;).Select
gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt; With ActiveCell.Characters(Start:=1, Length:=19).Font
gt;gt;gt; .Name = quot;Arialquot;
gt;gt;gt; .FontStyle = quot;Regularquot;
gt;gt;gt; .Size = 10
gt;gt;gt; .Strikethrough = False
gt;gt;gt; .Superscript = False
gt;gt;gt; .Subscript = False
gt;gt;gt; .OutlineFont = False
gt;gt;gt; .Shadow = False
gt;gt;gt; .Underline = xlUnderlineStyleNone
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; Range(quot;J1quot;).Select
gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Diff.quot;
gt;gt;gt; Range(quot;K1quot;).Select
gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Fillquot;
gt;gt;gt; Range(quot;L1quot;).Select
gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Cutquot;
gt;gt;gt; Range(quot;M1quot;).Select
gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Descriptionquot;
gt;gt;gt; Columns(quot;M:Mquot;).Select
gt;gt;gt; Selection.ColumnWidth = 20
gt;gt;gt; Columns(quot;G:Gquot;).Select
gt;gt;gt; Selection.ColumnWidth = 20
gt;gt;gt; Columns(quot;H:Hquot;).Select
gt;gt;gt; Selection.ColumnWidth = 12
gt;gt;gt; Columns(quot;I:Iquot;).Select
gt;gt;gt; Selection.ColumnWidth = 12
gt;gt;gt; ActiveWindow.SmallScroll Down:=-3
gt;gt;gt; Range(quot;G1:M20quot;).Select
gt;gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThick
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThick
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThick
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThick
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThin
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; With Selection.Borders(xlInsideHorizontal)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThin
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; Range(quot;G1:M1quot;).Select
gt;gt;gt; ActiveWindow.ScrollColumn = 3
gt;gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThick
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThick
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThick
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThick
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt; .Weight = xlThin
gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt; End With
gt;gt;gt; Range(quot;J22quot;).Select
gt;gt;gt; ActiveWindow.SmallScroll Down:=-12
gt;gt;gt; Rows(quot;1:1quot;).EntireRow.AutoFit
gt;gt;gt; Range(quot;G1:M1quot;).Select
gt;gt;gt; With Selection
gt;gt;gt; .HorizontalAlignment = xlCenter
gt;gt;gt; .VerticalAlignment = xlBottom
gt;gt;gt; .Orientation = 0
gt;gt;gt; .AddIndent = False
gt;gt;gt; .ShrinkToFit = False
gt;gt;gt; .MergeCells = False
gt;gt;gt; End With
gt;gt;gt; Range(quot;G1quot;).Select
gt;gt;gt; End Sub
gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;Joequot; wrote:
gt;gt;gt;
gt;gt;gt;gt; I am relatively new to recording macros with excel 2003. The issue I
gt;gt;gt;gt; have is
gt;gt;gt;gt; that I have the macro recorded and working properly. This macro will be
gt;gt;gt;gt; used
gt;gt;gt;gt; frequently in the same worksheet. It is basic formatting, text and
gt;gt;gt;gt; formulas.
gt;gt;gt;gt; What I would like to have happen is that when I run the maro it wll
gt;gt;gt;gt; drop down
gt;gt;gt;gt; vertically within the same columns from the range of cells above.
gt;gt;gt;gt; Basically
gt;gt;gt;gt; so the macro will cascade down vertically. Does this make sense. Can
gt;gt;gt;gt; anyone
gt;gt;gt;gt; help me. Thanks.
gt;gt;
gt;gt;
gt;
gt;
Don
I used .Borders as you said (see code below) and got an quot;Invalid use of
propertyquot; error on the word quot;.Bordersquot;. Thanks for your help. Otto
Sub TestBorders()
Range(quot;G1:M20quot;) _
.Borders LineStyle:=xlContinuous, Weight:=xlThick
End Sub
quot;Don Guillettquot; gt; wrote in message
...
gt; .borders instead of .borderaround
gt;
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Otto Moehrbachquot; gt; wrote in message
gt; ...
gt;gt; Don
gt;gt; Your code puts a border around the entire range G1:M20 as well as
gt;gt; G1:M1, but no borders around each cell in the range. The OP's code puts
gt;gt; borders around each cell so I assume that's what he wanted. Is there a
gt;gt; code that puts borders around each cell in a range without using the
gt;gt; laborious:
gt;gt; Borders(xlEdgeLeft)
gt;gt; Borders(xlEdgeRight)
gt;gt; Borders(xlEdgeTop)
gt;gt; Borders(xlEdgeBottom)
gt;gt; and all the attending lines of code that goes with each of these lines of
gt;gt; code (such as what you get when you record a macro and specify each
gt;gt; edge)?
gt;gt; Thanks for your help. Otto
gt;gt;
gt;gt; quot;Don Guillettquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; try this instead of your code. NO selections
gt;gt;gt;
gt;gt;gt; Sub trythisinstead()
gt;gt;gt; nr = Range(quot;a1quot;).SpecialCells(xlLastCell).Row
gt;gt;gt; 'MsgBox nr
gt;gt;gt; Rows(nr).RowHeight = 20
gt;gt;gt; Cells(nr, quot;gquot;) = quot;Remarksquot;
gt;gt;gt; Cells(nr, quot;Hquot;) = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt; Cells(nr, quot;Iquot;) = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt; Cells(nr, quot;Jquot;) = quot;Diff.quot;
gt;gt;gt; Cells(nr, quot;Kquot;) = quot;Fillquot;
gt;gt;gt; Cells(nr, quot;Lquot;) = quot;Cutquot;
gt;gt;gt; Cells(nr, quot;Mquot;) = quot;Descriptionquot;
gt;gt;gt; Columns(quot;gquot;).ColumnWidth = 20
gt;gt;gt; Columns(quot;Mquot;).ColumnWidth = 20
gt;gt;gt; Columns(quot;H:iquot;).ColumnWidth = 12
gt;gt;gt;
gt;gt;gt; Range(Cells(nr, quot;gquot;), Cells(nr 19, quot;mquot;)) _
gt;gt;gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt;gt;gt;
gt;gt;gt; With Range(Cells(nr, quot;gquot;), Cells(nr, quot;mquot;))
gt;gt;gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt;gt;gt; .HorizontalAlignment = xlCenter
gt;gt;gt; .VerticalAlignment = xlBottom
gt;gt;gt; End With
gt;gt;gt;
gt;gt;gt; Rows(nr).EntireRow.AutoFit
gt;gt;gt;
gt;gt;gt; End Sub
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; Don Guillett
gt;gt;gt; SalesAid Software
gt;gt;gt;
gt;gt;gt; quot;Joequot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt; Here is the VBA code. What I would like to have happen is everytime I
gt;gt;gt;gt; run
gt;gt;gt;gt; this macro is to have it format the cells directly underneath the
gt;gt;gt;gt; previously
gt;gt;gt;gt; formatted cells, So it just keeps going down the spreadsheet. For
gt;gt;gt;gt; example
gt;gt;gt;gt; this code will format cells in the range of G1 to M 20, the next time I
gt;gt;gt;gt; run
gt;gt;gt;gt; the macro it should format cells G21 to M40. Does this make sense?
gt;gt;gt;gt;
gt;gt;gt;gt; Sub NEWSHT()
gt;gt;gt;gt; '
gt;gt;gt;gt; ' NEWSHT Macro
gt;gt;gt;gt; ' Macro recorded 5/7/2006 by
gt;gt;gt;gt; '
gt;gt;gt;gt;
gt;gt;gt;gt; '
gt;gt;gt;gt; Rows(quot;1:1quot;).Select
gt;gt;gt;gt; Selection.RowHeight = 20
gt;gt;gt;gt; Range(quot;G1quot;).Select
gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Remarksquot;
gt;gt;gt;gt; Range(quot;H1quot;).Select
gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt; With ActiveCell.Characters(Start:=1, Length:=18).Font
gt;gt;gt;gt; .Name = quot;Arialquot;
gt;gt;gt;gt; .FontStyle = quot;Regularquot;
gt;gt;gt;gt; .Size = 10
gt;gt;gt;gt; .Strikethrough = False
gt;gt;gt;gt; .Superscript = False
gt;gt;gt;gt; .Subscript = False
gt;gt;gt;gt; .OutlineFont = False
gt;gt;gt;gt; .Shadow = False
gt;gt;gt;gt; .Underline = xlUnderlineStyleNone
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; Range(quot;I1quot;).Select
gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt; With ActiveCell.Characters(Start:=1, Length:=19).Font
gt;gt;gt;gt; .Name = quot;Arialquot;
gt;gt;gt;gt; .FontStyle = quot;Regularquot;
gt;gt;gt;gt; .Size = 10
gt;gt;gt;gt; .Strikethrough = False
gt;gt;gt;gt; .Superscript = False
gt;gt;gt;gt; .Subscript = False
gt;gt;gt;gt; .OutlineFont = False
gt;gt;gt;gt; .Shadow = False
gt;gt;gt;gt; .Underline = xlUnderlineStyleNone
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; Range(quot;J1quot;).Select
gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Diff.quot;
gt;gt;gt;gt; Range(quot;K1quot;).Select
gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Fillquot;
gt;gt;gt;gt; Range(quot;L1quot;).Select
gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Cutquot;
gt;gt;gt;gt; Range(quot;M1quot;).Select
gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Descriptionquot;
gt;gt;gt;gt; Columns(quot;M:Mquot;).Select
gt;gt;gt;gt; Selection.ColumnWidth = 20
gt;gt;gt;gt; Columns(quot;G:Gquot;).Select
gt;gt;gt;gt; Selection.ColumnWidth = 20
gt;gt;gt;gt; Columns(quot;H:Hquot;).Select
gt;gt;gt;gt; Selection.ColumnWidth = 12
gt;gt;gt;gt; Columns(quot;I:Iquot;).Select
gt;gt;gt;gt; Selection.ColumnWidth = 12
gt;gt;gt;gt; ActiveWindow.SmallScroll Down:=-3
gt;gt;gt;gt; Range(quot;G1:M20quot;).Select
gt;gt;gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt;gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt;gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThin
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; With Selection.Borders(xlInsideHorizontal)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThin
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; Range(quot;G1:M1quot;).Select
gt;gt;gt;gt; ActiveWindow.ScrollColumn = 3
gt;gt;gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt;gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt;gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt; .Weight = xlThin
gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt; End With
gt;gt;gt;gt; Range(quot;J22quot;).Select
gt;gt;gt;gt; ActiveWindow.SmallScroll Down:=-12
gt;gt;gt;gt; Rows(quot;1:1quot;).EntireRow.AutoFit
gt;gt;gt;gt; Range(quot;G1:M1quot;).Select
gt;gt;gt;gt; With Selection
gt;gt;gt;gt; .HorizontalAlignment = xlCenter
gt;gt;gt;gt; .VerticalAlignment = xlBottom
gt;gt;gt;gt; .Orientation = 0
gt;gt;gt;gt; .AddIndent = False
gt;gt;gt;gt; .ShrinkToFit = False
gt;gt;gt;gt; .MergeCells = False
gt;gt;gt;gt; End With
gt;gt;gt;gt; Range(quot;G1quot;).Select
gt;gt;gt;gt; End Sub
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt; quot;Joequot; wrote:
gt;gt;gt;gt;
gt;gt;gt;gt;gt; I am relatively new to recording macros with excel 2003. The issue I
gt;gt;gt;gt;gt; have is
gt;gt;gt;gt;gt; that I have the macro recorded and working properly. This macro will
gt;gt;gt;gt;gt; be used
gt;gt;gt;gt;gt; frequently in the same worksheet. It is basic formatting, text and
gt;gt;gt;gt;gt; formulas.
gt;gt;gt;gt;gt; What I would like to have happen is that when I run the maro it wll
gt;gt;gt;gt;gt; drop down
gt;gt;gt;gt;gt; vertically within the same columns from the range of cells above.
gt;gt;gt;gt;gt; Basically
gt;gt;gt;gt;gt; so the macro will cascade down vertically. Does this make sense. Can
gt;gt;gt;gt;gt; anyone
gt;gt;gt;gt;gt; help me. Thanks.
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
gt;.Borders LineStyle:=xlContinuous, Weight:=xlThick
you forgot a dot
..Borders.LineStyle:=xlContinuous, Weight:=xlThick
--
Don Guillett
SalesAid Software
quot;Otto Moehrbachquot; gt; wrote in message
...
gt; Don
gt; I used .Borders as you said (see code below) and got an quot;Invalid use of
gt; propertyquot; error on the word quot;.Bordersquot;. Thanks for your help. Otto
gt; Sub TestBorders()
gt; Range(quot;G1:M20quot;) _
gt; .Borders LineStyle:=xlContinuous, Weight:=xlThick
gt; End Sub
gt;
gt; quot;Don Guillettquot; gt; wrote in message
gt; ...
gt;gt; .borders instead of .borderaround
gt;gt;
gt;gt; --
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;Otto Moehrbachquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; Don
gt;gt;gt; Your code puts a border around the entire range G1:M20 as well as
gt;gt;gt; G1:M1, but no borders around each cell in the range. The OP's code puts
gt;gt;gt; borders around each cell so I assume that's what he wanted. Is there a
gt;gt;gt; code that puts borders around each cell in a range without using the
gt;gt;gt; laborious:
gt;gt;gt; Borders(xlEdgeLeft)
gt;gt;gt; Borders(xlEdgeRight)
gt;gt;gt; Borders(xlEdgeTop)
gt;gt;gt; Borders(xlEdgeBottom)
gt;gt;gt; and all the attending lines of code that goes with each of these lines
gt;gt;gt; of code (such as what you get when you record a macro and specify each
gt;gt;gt; edge)?
gt;gt;gt; Thanks for your help. Otto
gt;gt;gt;
gt;gt;gt; quot;Don Guillettquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt; try this instead of your code. NO selections
gt;gt;gt;gt;
gt;gt;gt;gt; Sub trythisinstead()
gt;gt;gt;gt; nr = Range(quot;a1quot;).SpecialCells(xlLastCell).Row
gt;gt;gt;gt; 'MsgBox nr
gt;gt;gt;gt; Rows(nr).RowHeight = 20
gt;gt;gt;gt; Cells(nr, quot;gquot;) = quot;Remarksquot;
gt;gt;gt;gt; Cells(nr, quot;Hquot;) = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt; Cells(nr, quot;Iquot;) = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt; Cells(nr, quot;Jquot;) = quot;Diff.quot;
gt;gt;gt;gt; Cells(nr, quot;Kquot;) = quot;Fillquot;
gt;gt;gt;gt; Cells(nr, quot;Lquot;) = quot;Cutquot;
gt;gt;gt;gt; Cells(nr, quot;Mquot;) = quot;Descriptionquot;
gt;gt;gt;gt; Columns(quot;gquot;).ColumnWidth = 20
gt;gt;gt;gt; Columns(quot;Mquot;).ColumnWidth = 20
gt;gt;gt;gt; Columns(quot;H:iquot;).ColumnWidth = 12
gt;gt;gt;gt;
gt;gt;gt;gt; Range(Cells(nr, quot;gquot;), Cells(nr 19, quot;mquot;)) _
gt;gt;gt;gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt;gt;gt;gt;
gt;gt;gt;gt; With Range(Cells(nr, quot;gquot;), Cells(nr, quot;mquot;))
gt;gt;gt;gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt;gt;gt;gt; .HorizontalAlignment = xlCenter
gt;gt;gt;gt; .VerticalAlignment = xlBottom
gt;gt;gt;gt; End With
gt;gt;gt;gt;
gt;gt;gt;gt; Rows(nr).EntireRow.AutoFit
gt;gt;gt;gt;
gt;gt;gt;gt; End Sub
gt;gt;gt;gt;
gt;gt;gt;gt; --
gt;gt;gt;gt; Don Guillett
gt;gt;gt;gt; SalesAid Software
gt;gt;gt;gt;
gt;gt;gt;gt; quot;Joequot; gt; wrote in message
gt;gt;gt;gt; ...
gt;gt;gt;gt;gt; Here is the VBA code. What I would like to have happen is everytime I
gt;gt;gt;gt;gt; run
gt;gt;gt;gt;gt; this macro is to have it format the cells directly underneath the
gt;gt;gt;gt;gt; previously
gt;gt;gt;gt;gt; formatted cells, So it just keeps going down the spreadsheet. For
gt;gt;gt;gt;gt; example
gt;gt;gt;gt;gt; this code will format cells in the range of G1 to M 20, the next time
gt;gt;gt;gt;gt; I run
gt;gt;gt;gt;gt; the macro it should format cells G21 to M40. Does this make sense?
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Sub NEWSHT()
gt;gt;gt;gt;gt; '
gt;gt;gt;gt;gt; ' NEWSHT Macro
gt;gt;gt;gt;gt; ' Macro recorded 5/7/2006 by
gt;gt;gt;gt;gt; '
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; '
gt;gt;gt;gt;gt; Rows(quot;1:1quot;).Select
gt;gt;gt;gt;gt; Selection.RowHeight = 20
gt;gt;gt;gt;gt; Range(quot;G1quot;).Select
gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Remarksquot;
gt;gt;gt;gt;gt; Range(quot;H1quot;).Select
gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt;gt; With ActiveCell.Characters(Start:=1, Length:=18).Font
gt;gt;gt;gt;gt; .Name = quot;Arialquot;
gt;gt;gt;gt;gt; .FontStyle = quot;Regularquot;
gt;gt;gt;gt;gt; .Size = 10
gt;gt;gt;gt;gt; .Strikethrough = False
gt;gt;gt;gt;gt; .Superscript = False
gt;gt;gt;gt;gt; .Subscript = False
gt;gt;gt;gt;gt; .OutlineFont = False
gt;gt;gt;gt;gt; .Shadow = False
gt;gt;gt;gt;gt; .Underline = xlUnderlineStyleNone
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; Range(quot;I1quot;).Select
gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt;gt; With ActiveCell.Characters(Start:=1, Length:=19).Font
gt;gt;gt;gt;gt; .Name = quot;Arialquot;
gt;gt;gt;gt;gt; .FontStyle = quot;Regularquot;
gt;gt;gt;gt;gt; .Size = 10
gt;gt;gt;gt;gt; .Strikethrough = False
gt;gt;gt;gt;gt; .Superscript = False
gt;gt;gt;gt;gt; .Subscript = False
gt;gt;gt;gt;gt; .OutlineFont = False
gt;gt;gt;gt;gt; .Shadow = False
gt;gt;gt;gt;gt; .Underline = xlUnderlineStyleNone
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; Range(quot;J1quot;).Select
gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Diff.quot;
gt;gt;gt;gt;gt; Range(quot;K1quot;).Select
gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Fillquot;
gt;gt;gt;gt;gt; Range(quot;L1quot;).Select
gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Cutquot;
gt;gt;gt;gt;gt; Range(quot;M1quot;).Select
gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Descriptionquot;
gt;gt;gt;gt;gt; Columns(quot;M:Mquot;).Select
gt;gt;gt;gt;gt; Selection.ColumnWidth = 20
gt;gt;gt;gt;gt; Columns(quot;G:Gquot;).Select
gt;gt;gt;gt;gt; Selection.ColumnWidth = 20
gt;gt;gt;gt;gt; Columns(quot;H:Hquot;).Select
gt;gt;gt;gt;gt; Selection.ColumnWidth = 12
gt;gt;gt;gt;gt; Columns(quot;I:Iquot;).Select
gt;gt;gt;gt;gt; Selection.ColumnWidth = 12
gt;gt;gt;gt;gt; ActiveWindow.SmallScroll Down:=-3
gt;gt;gt;gt;gt; Range(quot;G1:M20quot;).Select
gt;gt;gt;gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt;gt;gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThin
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; With Selection.Borders(xlInsideHorizontal)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThin
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; Range(quot;G1:M1quot;).Select
gt;gt;gt;gt;gt; ActiveWindow.ScrollColumn = 3
gt;gt;gt;gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt;gt;gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt; .Weight = xlThin
gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; Range(quot;J22quot;).Select
gt;gt;gt;gt;gt; ActiveWindow.SmallScroll Down:=-12
gt;gt;gt;gt;gt; Rows(quot;1:1quot;).EntireRow.AutoFit
gt;gt;gt;gt;gt; Range(quot;G1:M1quot;).Select
gt;gt;gt;gt;gt; With Selection
gt;gt;gt;gt;gt; .HorizontalAlignment = xlCenter
gt;gt;gt;gt;gt; .VerticalAlignment = xlBottom
gt;gt;gt;gt;gt; .Orientation = 0
gt;gt;gt;gt;gt; .AddIndent = False
gt;gt;gt;gt;gt; .ShrinkToFit = False
gt;gt;gt;gt;gt; .MergeCells = False
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt; Range(quot;G1quot;).Select
gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; quot;Joequot; wrote:
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; I am relatively new to recording macros with excel 2003. The issue I
gt;gt;gt;gt;gt;gt; have is
gt;gt;gt;gt;gt;gt; that I have the macro recorded and working properly. This macro will
gt;gt;gt;gt;gt;gt; be used
gt;gt;gt;gt;gt;gt; frequently in the same worksheet. It is basic formatting, text and
gt;gt;gt;gt;gt;gt; formulas.
gt;gt;gt;gt;gt;gt; What I would like to have happen is that when I run the maro it wll
gt;gt;gt;gt;gt;gt; drop down
gt;gt;gt;gt;gt;gt; vertically within the same columns from the range of cells above.
gt;gt;gt;gt;gt;gt; Basically
gt;gt;gt;gt;gt;gt; so the macro will cascade down vertically. Does this make sense. Can
gt;gt;gt;gt;gt;gt; anyone
gt;gt;gt;gt;gt;gt; help me. Thanks.
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
Don
That doesn't work either. I get an error as soon as I type the dot and
leave that line. When I run the macro I get a Syntax error. Without the
dot I get an quot;Invalid use of propertyquot; error. The dot code I used is:
Sub TestBorders()
Range(quot;G1:M20quot;) _
.Borders.LineStyle:=xlContinuous, Weight:=xlThick
End Sub
The same code without the dot is:
Sub TestBorders()
Range(quot;G1:M20quot;) _
.Borders LineStyle:=xlContinuous, Weight:=xlThick
End Sub
I appreciate the time you are spending on this. Thanks. Otto
quot;Don Guillettquot; gt; wrote in message
...
gt; gt;.Borders LineStyle:=xlContinuous, Weight:=xlThick
gt; you forgot a dot
gt; .Borders.LineStyle:=xlContinuous, Weight:=xlThick
gt; --
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;Otto Moehrbachquot; gt; wrote in message
gt; ...
gt;gt; Don
gt;gt; I used .Borders as you said (see code below) and got an quot;Invalid use
gt;gt; of propertyquot; error on the word quot;.Bordersquot;. Thanks for your help. Otto
gt;gt; Sub TestBorders()
gt;gt; Range(quot;G1:M20quot;) _
gt;gt; .Borders LineStyle:=xlContinuous, Weight:=xlThick
gt;gt; End Sub
gt;gt;
gt;gt; quot;Don Guillettquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; .borders instead of .borderaround
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; Don Guillett
gt;gt;gt; SalesAid Software
gt;gt;gt;
gt;gt;gt; quot;Otto Moehrbachquot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt; Don
gt;gt;gt;gt; Your code puts a border around the entire range G1:M20 as well as
gt;gt;gt;gt; G1:M1, but no borders around each cell in the range. The OP's code
gt;gt;gt;gt; puts borders around each cell so I assume that's what he wanted. Is
gt;gt;gt;gt; there a code that puts borders around each cell in a range without
gt;gt;gt;gt; using the laborious:
gt;gt;gt;gt; Borders(xlEdgeLeft)
gt;gt;gt;gt; Borders(xlEdgeRight)
gt;gt;gt;gt; Borders(xlEdgeTop)
gt;gt;gt;gt; Borders(xlEdgeBottom)
gt;gt;gt;gt; and all the attending lines of code that goes with each of these lines
gt;gt;gt;gt; of code (such as what you get when you record a macro and specify each
gt;gt;gt;gt; edge)?
gt;gt;gt;gt; Thanks for your help. Otto
gt;gt;gt;gt;
gt;gt;gt;gt; quot;Don Guillettquot; gt; wrote in message
gt;gt;gt;gt; ...
gt;gt;gt;gt;gt; try this instead of your code. NO selections
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Sub trythisinstead()
gt;gt;gt;gt;gt; nr = Range(quot;a1quot;).SpecialCells(xlLastCell).Row
gt;gt;gt;gt;gt; 'MsgBox nr
gt;gt;gt;gt;gt; Rows(nr).RowHeight = 20
gt;gt;gt;gt;gt; Cells(nr, quot;gquot;) = quot;Remarksquot;
gt;gt;gt;gt;gt; Cells(nr, quot;Hquot;) = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt;gt; Cells(nr, quot;Iquot;) = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt;gt; Cells(nr, quot;Jquot;) = quot;Diff.quot;
gt;gt;gt;gt;gt; Cells(nr, quot;Kquot;) = quot;Fillquot;
gt;gt;gt;gt;gt; Cells(nr, quot;Lquot;) = quot;Cutquot;
gt;gt;gt;gt;gt; Cells(nr, quot;Mquot;) = quot;Descriptionquot;
gt;gt;gt;gt;gt; Columns(quot;gquot;).ColumnWidth = 20
gt;gt;gt;gt;gt; Columns(quot;Mquot;).ColumnWidth = 20
gt;gt;gt;gt;gt; Columns(quot;H:iquot;).ColumnWidth = 12
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Range(Cells(nr, quot;gquot;), Cells(nr 19, quot;mquot;)) _
gt;gt;gt;gt;gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; With Range(Cells(nr, quot;gquot;), Cells(nr, quot;mquot;))
gt;gt;gt;gt;gt; .BorderAround LineStyle:=xlContinuous, Weight:=xlThick
gt;gt;gt;gt;gt; .HorizontalAlignment = xlCenter
gt;gt;gt;gt;gt; .VerticalAlignment = xlBottom
gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; Rows(nr).EntireRow.AutoFit
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; --
gt;gt;gt;gt;gt; Don Guillett
gt;gt;gt;gt;gt; SalesAid Software
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt; quot;Joequot; gt; wrote in message
gt;gt;gt;gt;gt; ...
gt;gt;gt;gt;gt;gt; Here is the VBA code. What I would like to have happen is everytime I
gt;gt;gt;gt;gt;gt; run
gt;gt;gt;gt;gt;gt; this macro is to have it format the cells directly underneath the
gt;gt;gt;gt;gt;gt; previously
gt;gt;gt;gt;gt;gt; formatted cells, So it just keeps going down the spreadsheet. For
gt;gt;gt;gt;gt;gt; example
gt;gt;gt;gt;gt;gt; this code will format cells in the range of G1 to M 20, the next time
gt;gt;gt;gt;gt;gt; I run
gt;gt;gt;gt;gt;gt; the macro it should format cells G21 to M40. Does this make sense?
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; Sub NEWSHT()
gt;gt;gt;gt;gt;gt; '
gt;gt;gt;gt;gt;gt; ' NEWSHT Macro
gt;gt;gt;gt;gt;gt; ' Macro recorded 5/7/2006 by
gt;gt;gt;gt;gt;gt; '
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; '
gt;gt;gt;gt;gt;gt; Rows(quot;1:1quot;).Select
gt;gt;gt;gt;gt;gt; Selection.RowHeight = 20
gt;gt;gt;gt;gt;gt; Range(quot;G1quot;).Select
gt;gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Remarksquot;
gt;gt;gt;gt;gt;gt; Range(quot;H1quot;).Select
gt;gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Proposedquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt;gt;gt; With ActiveCell.Characters(Start:=1, Length:=18).Font
gt;gt;gt;gt;gt;gt; .Name = quot;Arialquot;
gt;gt;gt;gt;gt;gt; .FontStyle = quot;Regularquot;
gt;gt;gt;gt;gt;gt; .Size = 10
gt;gt;gt;gt;gt;gt; .Strikethrough = False
gt;gt;gt;gt;gt;gt; .Superscript = False
gt;gt;gt;gt;gt;gt; .Subscript = False
gt;gt;gt;gt;gt;gt; .OutlineFont = False
gt;gt;gt;gt;gt;gt; .Shadow = False
gt;gt;gt;gt;gt;gt; .Underline = xlUnderlineStyleNone
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; Range(quot;I1quot;).Select
gt;gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Exisitingquot; amp; Chr(10) amp; quot;Elevationquot;
gt;gt;gt;gt;gt;gt; With ActiveCell.Characters(Start:=1, Length:=19).Font
gt;gt;gt;gt;gt;gt; .Name = quot;Arialquot;
gt;gt;gt;gt;gt;gt; .FontStyle = quot;Regularquot;
gt;gt;gt;gt;gt;gt; .Size = 10
gt;gt;gt;gt;gt;gt; .Strikethrough = False
gt;gt;gt;gt;gt;gt; .Superscript = False
gt;gt;gt;gt;gt;gt; .Subscript = False
gt;gt;gt;gt;gt;gt; .OutlineFont = False
gt;gt;gt;gt;gt;gt; .Shadow = False
gt;gt;gt;gt;gt;gt; .Underline = xlUnderlineStyleNone
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; Range(quot;J1quot;).Select
gt;gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Diff.quot;
gt;gt;gt;gt;gt;gt; Range(quot;K1quot;).Select
gt;gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Fillquot;
gt;gt;gt;gt;gt;gt; Range(quot;L1quot;).Select
gt;gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Cutquot;
gt;gt;gt;gt;gt;gt; Range(quot;M1quot;).Select
gt;gt;gt;gt;gt;gt; ActiveCell.FormulaR1C1 = quot;Descriptionquot;
gt;gt;gt;gt;gt;gt; Columns(quot;M:Mquot;).Select
gt;gt;gt;gt;gt;gt; Selection.ColumnWidth = 20
gt;gt;gt;gt;gt;gt; Columns(quot;G:Gquot;).Select
gt;gt;gt;gt;gt;gt; Selection.ColumnWidth = 20
gt;gt;gt;gt;gt;gt; Columns(quot;H:Hquot;).Select
gt;gt;gt;gt;gt;gt; Selection.ColumnWidth = 12
gt;gt;gt;gt;gt;gt; Columns(quot;I:Iquot;).Select
gt;gt;gt;gt;gt;gt; Selection.ColumnWidth = 12
gt;gt;gt;gt;gt;gt; ActiveWindow.SmallScroll Down:=-3
gt;gt;gt;gt;gt;gt; Range(quot;G1:M20quot;).Select
gt;gt;gt;gt;gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt;gt;gt;gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThin
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlInsideHorizontal)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThin
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; Range(quot;G1:M1quot;).Select
gt;gt;gt;gt;gt;gt; ActiveWindow.ScrollColumn = 3
gt;gt;gt;gt;gt;gt; Selection.Borders(xlDiagonalDown).LineStyle = xlNone
gt;gt;gt;gt;gt;gt; Selection.Borders(xlDiagonalUp).LineStyle = xlNone
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeLeft)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeTop)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeBottom)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlEdgeRight)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThick
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; With Selection.Borders(xlInsideVertical)
gt;gt;gt;gt;gt;gt; .LineStyle = xlContinuous
gt;gt;gt;gt;gt;gt; .Weight = xlThin
gt;gt;gt;gt;gt;gt; .ColorIndex = xlAutomatic
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; Range(quot;J22quot;).Select
gt;gt;gt;gt;gt;gt; ActiveWindow.SmallScroll Down:=-12
gt;gt;gt;gt;gt;gt; Rows(quot;1:1quot;).EntireRow.AutoFit
gt;gt;gt;gt;gt;gt; Range(quot;G1:M1quot;).Select
gt;gt;gt;gt;gt;gt; With Selection
gt;gt;gt;gt;gt;gt; .HorizontalAlignment = xlCenter
gt;gt;gt;gt;gt;gt; .VerticalAlignment = xlBottom
gt;gt;gt;gt;gt;gt; .Orientation = 0
gt;gt;gt;gt;gt;gt; .AddIndent = False
gt;gt;gt;gt;gt;gt; .ShrinkToFit = False
gt;gt;gt;gt;gt;gt; .MergeCells = False
gt;gt;gt;gt;gt;gt; End With
gt;gt;gt;gt;gt;gt; Range(quot;G1quot;).Select
gt;gt;gt;gt;gt;gt; End Sub
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt; quot;Joequot; wrote:
gt;gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;gt;gt; I am relatively new to recording macros with excel 2003. The issue I
gt;gt;gt;gt;gt;gt;gt; have is
gt;gt;gt;gt;gt;gt;gt; that I have the macro recorded and working properly. This macro will
gt;gt;gt;gt;gt;gt;gt; be used
gt;gt;gt;gt;gt;gt;gt; frequently in the same worksheet. It is basic formatting, text and
gt;gt;gt;gt;gt;gt;gt; formulas.
gt;gt;gt;gt;gt;gt;gt; What I would like to have happen is that when I run the maro it wll
gt;gt;gt;gt;gt;gt;gt; drop down
gt;gt;gt;gt;gt;gt;gt; vertically within the same columns from the range of cells above.
gt;gt;gt;gt;gt;gt;gt; Basically
gt;gt;gt;gt;gt;gt;gt; so the macro will cascade down vertically. Does this make sense. Can
gt;gt;gt;gt;gt;gt;gt; anyone
gt;gt;gt;gt;gt;gt;gt; help me. Thanks.
gt;gt;gt;gt;gt;
gt;gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;gt;
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
留言列表
