• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

Macro: Inserting a new row, keep the formatting - change fill for one cell

Attached I have a macro for inserting a new row into a spreadsheet. This works fine but I would like the macro to preset the cell i column K with a fill color (using RGB value). This fill color will later be changed by a macro running in Worksheet_change.


Sub InsertARow()
     'make new row
    ActiveCell.EntireRow.Insert shift:=xlUp
     'copy the row above
    ActiveCell.Offset(1, 0).EntireRow.Copy Cells(ActiveCell.Row, 1)
    On Error Resume Next
     'clear every cell in the new line that does not have a formula
    ActiveCell.EntireRow.SpecialCells(xlCellTypeConstants, 23).ClearContents
End Sub

Open in new window

1 Solution
philip m o'brienCommented:
Add this just before the "End Sub":
Cells(ActiveCell.Row, 11).Interior.Color = RGB(200, 160, 35)
and change the RGB value to whatever colour you want to use.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now