vba to insert new row AND highlight NEWLY INSERTED ROW

**wanty** · 06-01-2013, 08:26 PM

Is it possible to not only use vba to insert a new row in a sheet, copy down the formula and formats from the row above the newly inserted row BUT TO ALSO HIGHLIGHT the NEWLY inserted row so that it stands out ???

**AlphaFrog** · 06-01-2013, 11:40 PM

Sub Insert_Row_and_Highlight()
    ActiveCell.EntireRow.Copy
    ActiveCell.Offset(1).EntireRow.Insert CopyOrigin:=True
    On Error Resume Next
    ActiveCell.Offset(1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
    On Error GoTo 0
    ActiveCell.Offset(1).EntireRow.Interior.ColorIndex = 36
End Sub

**wanty** · 06-01-2013, 11:56 PM

Thanks Alpha, what I would really like to apply this to is the code below so that any new row inserted becomes highlighted, copies both the formatting and formulas down from the copied row and stands out clearly;

copied row example, line 10 in full code at the bottom of page

Rows("3:3").Copy

See below full code with which I'd like to apply highlighting ;

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long, rng As Range, c As Range
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
lr = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row
Set rng = ActiveSheet.Range("E2:E" & lr)
If Not Intersect(Target, rng) Is Nothing Then
    For Each c In rng
        If c.Value < Target.Value And c.Offset(1, 0).Value > Target.Value Then
            Rows("3:3").Copy
            c.Offset(1, 0).EntireRow.Insert
            
            Exit For
        End If
    Next
End If
Application.EnableEvents = True
End Sub