CODEWITHSUNDEEP
vbaexcel
Pericles Faliagas
Pericles Faliagas

Reputation: 636

Delete entire row, if a cell has a background color

I am trying to delete all the rows of which the cell in column D has a background color. I have written the code below, but everytime I run it, it runs indefinitely until it eventually crashes. Both ws1 and lastrow2 are well defined (I mention it to clear this possibility of why my macro is not running)

With ws1
    lastrow2 = ws1.Range("A" & Rows.Count).End(xlUp).Row

    For i = lastrow2 To 2 Step -1
        nodel = False

        If .Cells(i, "D").Interior.ColorIndex = 0 Then
            nodel = True
        End If

        If Not nodel Then
            .Rows(i).EntireRow.Delete
        End If
    Next i
End With

Upvotes: 2

Views: 336

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

Don't use 0:

Sub qwerty()
    Dim ws1 As Worksheet: Set ws1 = ActiveSheet
    Dim Nodel As Boolean
    With ws1
        lastrow2 = ws1.Range("A" & Rows.Count).End(xlUp).Row
        For i = lastrow2 To 2 Step -1
            Nodel = False
            If .Cells(i, "D").Interior.ColorIndex = -4142 Then
               Nodel = True
            End If
            If Not Nodel Then
                .Rows(i).EntireRow.Delete
            End If
        Next i
    End With
End Sub

EDIT#1:

If you want to retain cells with a white background, first verify that "white" corresponds to "colorindex=2" and then use 2 in place of -4142 in the code.

Upvotes: 5

Related Questions