Conditional formatting cell error

Question

The code I have below is checking two worksheets in order to see if the values inserted in the specific column are similar. For example, it looks to see if the values inserted in column A from sheet1 are the same as the values inserted in sheet2 column B. If yes, then the cells in sheet1 column A remain 'white' otherwise, they turn 'red'. The code works without any problems and really fast.

My problem is the following. Lets say:

• I need to insert a value in sheet1 - Column A, cell A2 to A5 that match the ones from sheet2 Column B.
• sheet2 column B has the following values: car, house, garden, city, country.
• If in A2 I write car, A3 I leave empty, A4 country and A5 car, then A2, A4 and A5 will remain 'white' because those values are in sheet2 - Column B. However, A3 turns red even though the cell is empty - this my problem. How can I make the code to not take into consideration if that cell is empty? It should not turn red because I left the cell empty and it is not comparing anything...

I hope I explain myself somehow. Thanks for your help!

Private Sub CommandButton1_Click()

Set wb = Excel.ActiveWorkbook
Set aRec = wb.Worksheets(1)
Set bRec = wb.Worksheets(2)

Application.ScreenUpdating = False

For a = 2 To aRec.Cells(Rows.Count, "A").End(xlUp).Row
    match = Application.match(aRec.Cells(c, 1).Value, bRec.Columns(2), 0)

If IsError(match) Then
     aRec.Cells(c, 1).Interior.Color = RGB(255, 0, 0)

Else
    aRec.Cells(c, 1).Interior.Color = RGB(255, 255, 255)

End If

    Next c

End Sub

Answer 1

Like?

Private Sub CommandButton1_Click()

Set wb = Excel.ActiveWorkbook
Set aRec = wb.Worksheets(1)
Set bRec = wb.Worksheets(2)

Application.ScreenUpdating = False

For a = 2 To aRec.Cells(Rows.Count, "A").End(xlUp).Row
    Match = Application.Match(aRec.Cells(a, 1).Value, bRec.Columns(2), 0)

    If IsError(Match) And Not IsEmpty(aRec.Cells(a, 1)) Then
        aRec.Cells(a, 1).Interior.Color = RGB(255, 0, 0)

    Else
        aRec.Cells(a, 1).Interior.Color = RGB(255, 255, 255)

    End If

Next a

End Sub

With correct loop variable, Option Explicit, type declarations and switching screenupdating back on

Option Explicit

Private Sub CommandButton1_Click()

Dim wb As Workbook
Dim aRec As Worksheet
Dim bRec As Worksheet
Dim a As Long
Dim Match As Variant

Set wb = ActiveWorkbook
Set aRec = wb.Worksheets(1)
Set bRec = wb.Worksheets(2)

Application.ScreenUpdating = False

For a = 2 To aRec.Cells(Rows.Count, "A").End(xlUp).Row

    Match = Application.Match(aRec.Cells(a, 1).Value, bRec.Columns(2), 0)

    If IsError(Match) And Not IsEmpty(aRec.Cells(a, 1)) Then
        aRec.Cells(a, 1).Interior.Color = RGB(255, 0, 0)
    Else
       aRec.Cells(a, 1).Interior.Color = RGB(255, 255, 255)
    End If

Next a

Application.ScreenUpdating = True

End Sub