VBA Conditional formatting to delete entire row when two variable are ture

Hi adtc,

Try this code. I've added a loop at the end of the Sub which loops backwards from the bottom most cell in Col J and checks its interior colour, then check the interior of the corresponding cell in Col E and if both conditions are met then it deletes the row.

You should also declare each variable as a range.

Give it a shot and post back please

Sub Duplicates()
' code to find duplicates in 2 different worksheets
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range
Dim cell1 As Range, cell2 As Range, cell3 As Range, cell4 As Range
Dim i As Long, lastCell2Row As Long

Set rng1 = Worksheets("Archive").Range("$B:$B")
Set rng2 = Worksheets("Agents").Range("J26:J1000")
Set rng3 = Worksheets("Archive").Range("$A:$A")
Set rng4 = Worksheets("Agents").Range("E26:E1000")
    For Each cell1 In rng1
        If IsEmpty(cell1.Value) Then Exit For
        'check for empty rows. If true then exit the program
            For Each cell2 In rng2
                If IsEmpty(cell2.Value) Then Exit For
                    If cell1.Value = cell2.Value Then
                    'compare data in cell1 and cell2 and then format if they have equal values.
                        cell1.Interior.ColorIndex = 4
                        cell1.Interior.Pattern = xlSolid
                        cell1.Borders.Weight = xlThick
                        cell2.Interior.ColorIndex = 4
                        cell2.Interior.Pattern = xlSolid
                        cell2.EntireRow.Resize(, 14).Interior.ColorIndex = 4
                    End If
            Next cell2
    Next cell1

    For Each cell3 In rng3
        If IsEmpty(cell3.Value) Then Exit For
        'check for empty rows. If true then exit the program
            For Each cell4 In rng4
                If IsEmpty(cell4.Value) Then Exit For
                    If cell3.Value = cell4.Value Then
                    'compare data in cell1 and cell2 and then format if they have equal values.
                        cell3.Interior.ColorIndex = 41
                        cell3.Borders.Weight = xlThick
                        cell3.Interior.Pattern = xlSolid
                        cell4.Interior.ColorIndex = 41
                        cell4.Borders.Weight = xlThick
                    End If
            Next cell4
    Next cell3

'get the last row in Col J with data in it
lastCell2Row = Worksheets("Agents").Range("J65536").End(xlUp).Row

'loop backwards and if the 2 conditions are met
'then delete the row
For i = lastCell2Row To 26 Step -1
    If Cells(i, "J").Interior.ColorIndex = 4 Then
        If Cells(i, "E").Interior.ColorIndex = 41 Then
            Rows(i & ":" & i).Delete
        End If
    End If
Next i
End Sub