Delete Row Based On Interior Colour

**bopsgtir** · 11-25-2012, 10:11 AM

Hi could someone point me in the right direction of where I am going wrong, I want to run through column B and if the cell is coloured Red then delete that row, I have the below code which seems to run through each cell but doesn't delete the row? Any help would be much appricated

Sub Delete()

With ActiveSheet

Dim LR As Long
Dim Counter As Long
'
LR = .Range("B" & Rows.Count).End(xlUp).Row

    For Counter = LR To 2 Step -1
        With Cells(Counter, "B")
            If Cells.Interior.color = 255 Then
                Rows(Counter).Delete Shift:=xlUp
            End If
        End With
    Next Counter
    End With

End Sub

**Palmetto** · 11-25-2012, 10:17 AM

Option Explicit

Sub Delete()

    Dim LR As Long
    Dim Counter As Long
    
    Application.ScreenUpdating = False
    
    With ActiveSheet
    
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    
        For Counter = LR To 2 Step -1
            With .Cells(Counter, "B")
                If .Interior.Color = 255 Then
                    .EntireRow.Delete
                End If
            End With
        Next Counter
    End With
        
    Application.ScreenUpdating = True

End Sub

**bopsgtir** · 11-25-2012, 10:42 AM

Ok its still not quite working, ive added the line before it aswell, im adding a conditional format to change the cell color to 255, then it wont delete them, if I manually go in and change the colour to 255 then the code works, so it seems that the condition format is not changing the color correctly??? im really confused now.

 Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$F2=TRUE"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .color = 255
    End With
    Selection.FormatConditions(1).StopIfTrue = False
With ActiveSheet


    For Counter = LR To 2 Step -1
        With Cells(Counter, "B")
            If .Interior.color = 255 Then
                .EntireRow.Delete
            End If
        End With
    Next Counter
    End With

End Sub