Deleting rows if one cell in the row is not a specific color

[dunk123]

Hi all

I have a bunch of customer data that is given to sales reps. Each customer account is on one row, with name, address etc in different columns. Using some basic testing principles, any missing or invalid data has its cell colored using:

With Selection.Interior
                .Color = 65535
            End With

I now wish to loop through all the rows, testing if any cells in the row are colored. If there are no cells in the entire row that are colored, then I wish to delete the row. I've conceptualized how to do it, but just not quite there.

Any help greatly appreciated. Many thanks.

 

[VoG]

Hi. Perhaps like this

Sub Test2()
Dim LR As Long, LC As Long, i As Long, j As Long, k As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
For i = LR To 1 Step -1
    k = 0
    For j = 1 To LC
        If Cells(i, j).Interior.Color = 65535 Then
            k = 1
            Exit For
        End If
    Next j
    If k = 0 Then Rows(i).Delete
Next i
End Sub

 

[dunk123]

Works like a dream. Awesome, thank you.