Inserting a pre-formatted row

YES, you can do this....What code did you use that
gave you errors ??

Ivan

****

Dear Ivan,

A question was raised sometime ago about inserting rows. The question was:

Could N rows be inserted automatically (say from 10th row) once value in
Cell (1,1) is changed to N ?

and the reply was:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then If IsNumeric(Target) Then
Rows(10).Resize(Target.Value).Insert
End Sub

another reply:

Put this in the worksheet change event

If Target.Address = "$A$3" Then
Rows("10:" & 10 + Target.Value).Select
Selection.Insert Shift:=xlDown
End If

The for both, my VBE shows the error occurs in the line:

If Target.Address = "$A$3"

even if I changed the reference cell to "$G$7", the cell that contain the variable.

The above also could not retrieve a pre-formatted row. It just inserts a blank row, right?

Thank you very much for your help.

Best regards,
Hock-doong.

I think the code could have read;

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And IsNumeric(Target) Then
Rows(10).Resize(Target.Value).Insert
'put code here to format
'the resized area
End If
End Sub

Then under the resize code put code to format
your area.

Ivan

Dear ivan,

Forgive my ignorance but where do I put the code?

Also how do I program the format or should I point it to a hidden pre-formatted row?

Thank ou very much for your help.

Best regards,
Hock-doong.

The code should go in the worksheet module
Right click on the sheet tab and select view code.
Place the code here.

To get the format code then try using the macro recorder and formating how you want it to look.
Turn it of and look @ the code.....this is where
you will have to make the code more generic to
suit the range you want.........post the formating
code if unsure of how todo this or email me.

Ivan

**

Dear Ivan,

Thank you very much for your help. I'll paste my code here:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And IsNumeric(Target) Then
Rows(10).Resize(Target.Value).Insert

'
' addrows Macro
' Macro recorded 12/11/00 by HO
'

'
Columns("A:A").Select
Selection.ColumnWidth = 0.92
Columns("B:B").Select
Selection.ColumnWidth = 8.43
Columns("C:C").Select
Selection.ColumnWidth = 8.43
Columns("D:D").Select
Selection.ColumnWidth = 10.14
Columns("E:E").Select
Selection.ColumnWidth = 6.86
Columns("F:F").Select
Selection.ColumnWidth = 0.92
Columns("G:G").Select
Selection.ColumnWidth = 9.57
Columns("H:H").Select
Selection.ColumnWidth = 9.57
Columns("I:N").Select
Selection.ColumnWidth = 9.57
ActiveWindow.ScrollColumn = 1
Range("B12:N12").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.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("B12").Select
With Selection.Interior
.ColorIndex = 10
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Range("C12:N12").Select
With Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 0
Range("C12:F12").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = True
End With
End If
End Sub

However, when I run the macro, I got stuck with at the line:

.LineStyle = xlContinuous

and could not proceed.

Could you please help?

Thank you very much in advance.

Best regards,
Hock-doong.