CODEWITHSUNDEEP
vbaexcel
Noomak
Noomak

Reputation: 371

Adding color with VBA to an entire excel row

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"

Upvotes: 0

Views: 269

Answers (3)

Dan Donoghue
Dan Donoghue

Reputation: 6186

Am i missing something here? Why is everyone looping?

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

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

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

Upvotes: 1

Kendall Outlaw
Kendall Outlaw

Reputation: 11

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

Upvotes: 0

Related Questions