CODEWITHSUNDEEP
excelvbaif-statementforeachdelete-row
Kaylynn Enright
Kaylynn Enright

Reputation: 27

VBA code to delete cells that contain a certain fill color?

I am new to VBA and am trying to delete cells that have a certain fill color. I cannot delete the entire row with cells that have fills because that would delete data that I need. I am trying to use a loop, but it does not delete all the cells that contain the fill color I specify, only some towards the top of my spreadsheet. If I run the macro several times, it will end up deleting all the cells with the color that I specified.

Range("A1:AF150").Select
For Each cell In Selection
    If cell.Interior.Color = RGB(112, 48, 160) Then
        cell.Delete
    End If
Next cell

Upvotes: 0

Views: 2167

Answers (1)

Daniel
Daniel

Reputation: 954

This is because when cell is deleted, data below shifts up and the loop goes to the next cell, omitting the one that just shifted up.

It's not possible to loop backwards using for each loop, but you can do it this way:

Set rng = Range("A1:AF150")
For i = rng.Cells.Count To 1 Step -1
    With rng.Item(i)
        If .Interior.Color = RGB(112, 48, 160) Then .Delete
    End With
Next

Also I would suggest setting a workbook object.

Also, are you sure you want to delete and not clear cell?

Upvotes: 2

Related Questions