Thread: Deleting rows based on Conditional Formatting

Hi,

I'm very new to VBA programming for excel macros and I need some help. I'm trying to get Excel to delete entire rows based on the conditional formating i'm using.

Basically, I want to get rid of all the rows that are of a certain color (let's say green, InteriorColor = 4). I've already come up with a way to delete rows based on color, but I have to take into account the Conditional formating i'm using.

Here's what I already have:

Option Explicit
Sub DeleteGreenRows()
Dim LR As Long, a As Long
Application.ScreenUpdating = False
LR = Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 1 Step -1
  If Cells(a, 1).FormatConditions.InteriorColor = 4 Then
    Rows(a).EntireRow.Delete
  End If
Next a
Application.ScreenUpdating = True
End Sub

Any help would be awesome. Thanks!

A better approach would be to put the conditional formatting formula in a separate column, and use that for both conditional formatting and as the test criteria for deletion.

Please change your QUOTE tags to CODE tags.

The conditional format would be Formula is =B1 or =NOT(B1), depending on what you want.

You can. Post the revised code.

Sub DeleteFalseRows()
    Dim iRow    As Long

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual

        For iRow = Cells(Rows.Count, 2).End(xlUp).Row To 1 Step -1
            If Cells(iRow, 2).Value = False Then Rows(iRow).Delete
        Next iRow

        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

When you delete a row, Excel automatically recalculates. There no need for it, and it will slow things down.

There is likewise no need to turn screenupdating off.

Would you please mark the thread as Solved?