CODEWITHSUNDEEP
excelvba
Dan
Dan

Reputation: 11

Coloring entire row after inserting row code ran

I have a code that inserts row after certain changes in numbers. How do I insert into the code to color the inserted rows?

  Sub InsertRowsAtValueChange()
    'Update 20140716
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert
    End If

    Next
    Application.ScreenUpdating = True

End Sub

Upvotes: 1

Views: 27

Answers (1)

Pawel Czyz
Pawel Czyz

Reputation: 1645

Try below. It is set to blue but you can use any color.

Sub InsertRowsAtValueChange()
    'Update 20140716
    Dim Rng As Range
    Dim WorkRng As Range

    xTitleId = "KutoolsforExcel"

    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

    Application.ScreenUpdating = False

    For i = WorkRng.Rows.Count To 2 Step -1
    If WorkRng.Cells(i, 1).Value <> WorkRng.Cells(i - 1, 1).Value Then
        WorkRng.Cells(i, 1).EntireRow.Insert

        'add color
        WorkRng.Cells(i, 1).EntireRow.Interior.Color = vbBlue
    End If

    Next
    Application.ScreenUpdating = True

End Sub

Upvotes: 1

Related Questions