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

**adtc** · 08-15-2011, 04:23 AM

Hi SOS,

Thanks for working out the re-wite for me. I have just given it a go but it doen't quite work.
The formatting works perfectly, it's just the loop for deleting the rows where [cel1= cell2 AND cell3=cell4].

At present, it looks like it is only deleting the very first row, from the bottom, where cell1=cell2 which it should not delete as it does not satisfy the criteria for deletion: [cel1= cell2 AND cell3=cell4].
After it deleted that first row, the loop/macro stops.

Hope this helps .
And thanks again for going to the trouble of helping my get this working.
I can send you a mock workbook if you need.

ADTC.

**adtc** · 08-15-2011, 10:09 AM

I've uploaded to mock workbooks.

One with VB code already in (for reference if needed)
http://dl.dropbox.com/u/8139608/VBmock.xls
And a second with No VB code if you like a clean slate, but need to see how it should look.
http://dl.dropbox.com/u/8139608/NO-VBmockmock.xls

Hope this helps
Thanks for all your hard work.
I look forward to seeing what you come-up with if you have some free time

ADTC

**SOS** · 08-15-2011, 04:43 PM

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