Maintain Row Number when Adding Rows

[wdgor]

I need to be able to insert rows in a sheet while maintaining the row number of the parent. In the example below, Line 3 is the parent and the 3 rows with "*" are the children. While this works, ideally I would like the 3 "*" to be replaced with the parent row number "3". This would be true no matter how many rows are added and the remainder of the row numbers would stay intact. In the example, row 4 is also a parent and the row under it with the "*" also represents a child - same need, i.e. the "*" would be replaced with the parent row number "4". In both cases, all the remaining numbered rows maintain their value. The code at the bottom is what is used to generate the child ("*") designations. Any suggestions would be appreciated. Thanks.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = "*"
Cancel = False

End Sub

 

[Micron]

Output examples are always better than describing - reduces the chance of misinterpretation (usually?). I take it that if you double click on 5, you want 5 to appear in the inserted row and not *. Try changing to
ActiveCell.Value = Target
or
ActiveCell.Value = Target.Value if you prefer.

 

[wdgor]

Micron - that was too simple. It works perfectly - I should have thought of it but very much appreciate your quick response. Thanks!!

I had a conditional format in place that turned the whole row "blue" with the "*". Any suggestion on how I might do the same thing with your approach? Thanks again. I am not a great VBA wizard.

 

[wdgor]

I think I solved it:

ActiveCell.EntireRow.Interior.ColorIndex = 20

Sorry for posting the question too soon.

 

[smozgur]

It works perfectly

The marked solution has been switched accordingly.