Insert New Row VBA Macro

Question

I want to insert a new row in MS Excel using a VBA macro and also modify the background color (i.e. Interior.ColorIndex) of specific cells in the new row.

I am using ActiveCell.Offset(1).EntireRow.Insert to insert a new row below the active cell, but I am unsure how to change the background color of specific cells in the new row.

For example:

If I inserted a new row (i.e. row 4) I would like to change the background color of cell B4 and C4 to be grey.

Any help would be most appreciated!

Regards

Martin

Answer 1

This will do it:

Sub insertRowAndHighlightCells()

    Dim rng As Range
    Dim rw As Long

    With ActiveCell
        rw = .Row
        .Offset(1).EntireRow.Insert
    End With

    Set rng = Rows(rw + 1)
    rng.Columns("B:C").Interior.Color = RGB(191, 191, 191)

End Sub

Edit

An even simpler version:

Sub insertRowAndHighlightCells()

    Dim rw As Long

    With ActiveCell
        rw = .Row
        .Offset(1).EntireRow.Insert
    End With

    Rows(rw + 1).Columns("B:C").Interior.Color = RGB(191, 191, 191)

End Sub

Answer 2

Why not use the activecell.offset(1,0) that you used to insert the row?

E.g.

Sub test()
    ActiveCell.Offset(1, 0).EntireRow.Insert shift:=xlDown
    ActiveSheet.Cells(ActiveCell.Offset(1, 0).Row, 2).Interior.ColorIndex = 15
    ActiveSheet.Cells(ActiveCell.Offset(1, 0).Row, 3).Interior.ColorIndex = 15
    'Alternatively:
    Dim colorRow as integer
    colorRow = ActiveCell.Offset(1,0).Row 'The inserted row)
    ActiveSheet.Range("B" & colorRow & ":C" & colorRow).Interior.ColorIndex = 15

End Sub