CODEWITHSUNDEEP
vbaexcel
Andreea
Andreea

Reputation: 65

skip empty cells in loop

I have the below macro that should highlight cells in sheet2 different from cells in sheet1. It's working well but I have a problem with it highlighting blank cell:

Private Sub Compare()

Dim shtARng As Range, cell As Range

With Worksheets("Sheet1")
Set shtARng = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp)) 
End With

With Worksheets("Sheet2") 
For Each cell In .Range("C2", .Cells(.Rows.Count, 9).End(xlUp))
    If shtARng.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then cell.Interior.ColorIndex = 3
Next cell
End With

End Sub

Upvotes: 2

Views: 10756

Answers (2)

David Glickman
David Glickman

Reputation: 790

Use IsEmpty to check if the cell is empty. If it is not empty then do your highlighting, otherwise loop on to the next cell.

For Each cell In .Range("C2", .Cells(.Rows.Count, 9).End(xlUp))
    If Not IsEmpty(cell) Then
        If shtARng.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then 
             cell.Interior.ColorIndex = 3
        End If
     End If
Next cell

Upvotes: 1

Jiminy Cricket
Jiminy Cricket

Reputation: 1377

Private Sub Compare()
Dim shtARng As Range, cell As Range

    With Worksheets("Sheet1")
        Set shtARng = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
    End With

    With Worksheets("Sheet2")
        For Each cell In .Range("C2", .Cells(.Rows.Count, 9).End(xlUp))
            'Only Proceed If The Length Of The Cell Is Bigger Than Zero
            If Len(cell) > 0 Then
                If shtARng.Find(What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False) Is Nothing Then cell.Interior.ColorIndex = 3
            End If
        Next cell
    End With

End Sub

Upvotes: 1

Related Questions