Using VBA how can I add a row and shade it

Microsoft Office excel 2007 - upgrade
November 30, 2010 at 18:28:50
Specs: Windows XP
I am hoping someone can set me on the right path here. I have some code that will add a blank row between rows of data that are different. Column A contains a descriptive field that the entire workboodk is sorted on. I want to add a blank line and shade that line when the descriptor changes. Here is the code I have. right now this works for what I want...mostly. What it is doing is shading the entire row, when all i need are columns A:AK...any thoughts or suggestions would be great.


Sub Insert_Row()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")
With wks
FirstRow = 2
LastRow = Sheets(1).Range("D" & Rows.Count).End(xlUp).Row


For iRow = LastRow To FirstRow Step -1
If LCase(.Cells(iRow, "A").Value) <> LCase(.Cells(iRow + 1, "A").Value) Then
.Rows(iRow + 1).EntireRow.Insert
If LCase(.Cells(iRow, "A").Value) <> LCase(.Cells(iRow + 1, "A").Value) Then
.Rows(iRow + 1).EntireRow.Interior.ColorIndex = 6

End If
End If
Next iRow
End With

End Sub


See More: Using VBA how can I add a row and shade it

Report •


#1
November 30, 2010 at 22:03:50
Replace this:

If LCase(.Cells(iRow, "A").Value) <> LCase(.Cells(iRow + 1, "A").Value) Then
.Rows(iRow + 1).EntireRow.Interior.ColorIndex = 6

With this:

If LCase(.Cells(iRow, "A").Value) <> LCase(.Cells(iRow + 1, "A").Value) Then
.Range(.Cells(iRow + 1, "A"), .Cells(iRow + 1, "AK")).Interior.ColorIndex = 6

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 1, 2010 at 04:44:52
That worked perfectly. Thank you!

Report •
Related Solutions


Ask Question