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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.