Adding color with VBA to an entire excel row

Question

I want to add n rows starting from A4 cell.

My A3 row is blue, so adding rows below it will add all blue rows.

This is my code:

Range("A4:A4").Select
Dim lRow As Long
For lRow = 4 To 14
    Cell.EntireRow.Interior.ColorIndex = xlNone
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove        
Next lRow

I got an error saying:

"Object required"

BruceWayne · Answer 1 · 2016-04-04T17:34:58.890

1

Range("A4:A4").Select
Dim lRow As Long
For lRow = 4 To 14
    Cells(lRow,1).EntireRow.Interior.ColorIndex = xlNone
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove        
Next lRow

You used Cell which doesn't mean anything to VBA. Use Cells([row],[column]) or even just Rows(lRow).EntireRow...

Also though, you're selecting a single cell (Range("A4:A4").Select) and this never changes, so only A4 would ever get a row inserted - is this what you want?

Edit: Without using .Select:

Dim myCell as Range
Dim lRow As Long

Set myCell = Range("A4")
For lRow = 4 To 14
    Cells(lRow,1).EntireRow.Interior.ColorIndex = xlNone
    myCell.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove        
Next lRow

edited Apr 04 '16 at 17:34

answered Apr 04 '16 at 17:17

BruceWayne

22,923
15
65
110

thx it works with: Range("A" & lRow).Select just at the beginning of the loop and then.... Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove _ Cells(lRow, 1).EntireRow.Interior.ColorIndex = xlNone – Noomak Apr 04 '16 at 17:23
1

@albert - please see my edit, as it's best practice to [avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) – BruceWayne Apr 04 '16 at 17:33

score 1 · Answer 2 · answered Apr 05 '16 at 03:36

1

Am i missing something here? Why is everyone looping?

Range("A4:A14").EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Range("A4:A14").EntireRow.Interior.ColorIndex = xlNone

answered Apr 05 '16 at 03:36

Dan Donoghue

6,056
2
18
36

score 0 · Answer 3 · edited Apr 04 '16 at 17:31

0

Try this:

Sub testing()

Dim i As Integer
Dim ws As Worksheet
Dim lRow As Long
Set ws = Sheets("Sheet1")

'Range("A4:A4").Select

lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row

For i = 4 To 14
    Range("A" & i & ":" & "A" & i).Interior.ColorIndex = 5

Next i

End Sub

edited Apr 04 '16 at 17:31

Scott Holtzman

27,099
5
37
72

answered Apr 04 '16 at 17:25

Kendall Outlaw

13
6

Sub testing() Dim i As Integer Dim ws As Worksheet Dim lRow As Long Set ws = Sheets("Sheet1") lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row For i = 4 To 14 Range("A" & i & ":" & "A" & i).Interior.ColorIndex = 5 Next i End Sub – Kendall Outlaw Apr 04 '16 at 17:25

Adding color with VBA to an entire excel row

3 Answers3