Conditionally delete cells using VBA

Dear Experts:

I got a big table where lots of cells/entries in Column D are red shaded (RGB 150, 54, 52).

Now I would like to achieve the following using a macro:

The macro is to loop thru all cells of Column D
If the macro hits a red shaded cell (RGB 150, 54, 52)  the macro is to delete the cell above, e.g.

a red-shaded cell occurs in Cell D18. The cell contents of D17 is to be deleted.

Help is much appreciated. Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
Rory ArchibaldCommented:
Assuming you meant clear:

Sub foo()
    Dim lastRow               As Long
    Dim Cell                  As Range
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    With ActiveSheet.Range("D1:D" & lastRow)
        .AutoFilter Field:=1, Criteria1:=RGB(150, _
                                             54, 52), Operator:=xlFilterCellColor
        If .SpecialCells(xlCellTypeVisible).Count > 1 Then
            For Each Cell In .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible).Cells
            Next Cell
        End If
    End With

End Sub

Open in new window

Rory ArchibaldCommented:
Do you want the cell above it cleared, or actually deleted? They are not the same thing. :)
Rob HensonFinance AnalystCommented:
How is the Red shading occurring, is it manual formatting or Conditional?

Does the red shading occur because of certain conditions? If so you could apply Condotional formatting to the cells checking the condition/value of the cell below and formatting the cell accordingly. You can then filter based on this new shading and delete the cells left visible. Highlighting the visible area and clearing or deleting rows will only affect the visible area.

Rob H
Andreas HermleTeam leaderAuthor Commented:
Hi Rob, thank you very much for your valuable help, but as a matter of fact, Rory delivered exactly what I was looking for.

Nevertheless thank you very much for your great help. Regards, Andreas
Andreas HermleTeam leaderAuthor Commented:
As always, great professional job. Thank you very much for your swift support. Regards, Andreas
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.