CODEWITHSUNDEEP
vbaexcelcolors
D.Mitchell
D.Mitchell

Reputation: 1

Remove rows with a specific cell color - CONDITIONAL FORMAT COLOR

Rule 1: Delete Row IF cells in column A are Green AND Column J contains the term "SA Comments -" just contains not exact THEN delete row. Then Rule 2: Delete Row IF cells in column A are Red AND Column J DOES NOT contain the term "SA Comments -" THEN delete row. Then Rule 3: IF a cell in column J has no value THEN Add the term "Sa Comments -" to any cell that has no value.

These cells are filled red with conditional formatting?

I understand I need to user Instr? If am not looking for an exact match.

Sub sbDelete_Rows_Based_On_Cell_Color()

Dim lRow As Long
Dim iCntr As Long

lRow = 9999
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1).Interior.ColorIndex = xlNone And Cells(iCntr, 10).Value = "SA Comments -" Then
    '2 = None
    Rows(iCntr).Delete

    ElseIf Cells(iCntr, 1).Interior.ColorIndex = 3 And Cells(iCntr, 10).Value <> "SA Comments -" Then
        '4 = Red
        Rows(iCntr).Delete
    End If

Next iCntr

End Sub

Upvotes: 0

Views: 1464

Answers (1)

Shai Rado
Shai Rado

Reputation: 33682

The code below works for me, just make sure you are having Interior.Color of 4 in the cells in Column A.

Not sure, but you are looking to have an exact match or partial match of "SA Comments -" ?

Are you looking for text in Column J while checking the interior color in Column A ?

Private Sub CommandButton21_Click()

Dim lastrow As Long

With ThisWorkbook.Worksheets("Outstanding Aged Incidents")
    ' reading last row with data from Column A
    lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row

    For i = lastrow To 2 Step -1            
        If .Cells(i, 10).Value = "SA Comments -" And .Cells(i, 1).Interior.Color = 4 Then
            .Rows(i).Delete
        End If        
    Next i
End With

End Sub

Edit 1: If you are looking for partial match of "SA Comments -" , you have 2 alternatives:

Instr - use the line below:

If InStr(.Cells(iCntr, 10).Value, "SA Comments -") > 0 Then

Like - use the line below:

If .Cells(iCntr, 10).Value Like "*SA Comments -*" Then

Edit 2: Modified code to suit the code uploaded by PO since original post.

Sub sbDelete_Rows_Based_On_Cell_Color()

Dim lRow As Long
Dim iCntr As Long

lRow = 9999
For iCntr = lRow To 1 Step -1
    If Cells(iCntr, 1).Interior.ColorIndex = xlNone And InStr(Cells(iCntr, 10).Value, "SA Comments -") > 0 Then
    '2 = None
    Rows(iCntr).Delete

    ElseIf Cells(iCntr, 1).Interior.ColorIndex = 3 And InStr(Cells(iCntr, 10).Value, "SA Comments -") > 0 Then
        '4 = Red
        Rows(iCntr).Delete
    End If

Next iCntr

End Sub

Upvotes: 1

Related Questions