Thread: run-time error '1004 Unable to get Match property of WorksheetFunction class

hello,

i'm trying to write a macro that colors in cells in a table if the number in the cell matches with a number in another column, but i get run time error '1004.

any idea? i tried so many things but it seems that i can't re-use range() in a for loop? can someone please help me??????

exl044

Sub colorMyCell()
        
    For i = 2 To 31
        
        For j = 6 To 15
            
            MyCellValue = Cells(i, j).Value
            
            
            If (Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(MyCellValue, Range("A2:A31"))) <> False) Then
                Cells(i, j).Interior.ColorIndex = 38    'colors cell with this color if the value matches column A values
            
            End If
            
       
            If (Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(MyCellValue, Range("B2:B31"))) <> False) Then
                Cells(i, j).Interior.ColorIndex = 40  'colors cell with this color if the value matches column B values
                
            End If
            
            If (Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(MyCellValue, Range("C2:C31")))) Then
                Cells(i, j).Interior.ColorIndex = 42    'colors cell with this color if the value matches column C values
                
            End If
            
            If (Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(MyCellValue, Range("D2:D31")))) Then
                Cells(i, j).Interior.ColorIndex = 44    'colors cell with this color if the value matches column D values
                
            End If
            
        Next j
            
    Next i

End Sub

Hi Exl044, welcome to the forum.

Rather than using Application.WorksheetFunction.Match, just use Application.Match. Below is your code with a few minor adjustments and should work as intended.

Sub colorMyCell()
Dim i As Long, j As Long, MyCellValue As Long
For i = 2 To 31
    For j = 6 To 15
        MyCellValue = Cells(i, j).Value
        With Application
            If Not IsError(.Match(MyCellValue, Range("A2:A31"), 0)) Then
                Cells(i, j).Interior.ColorIndex = 38    'colors cell with this color if the value matches column A values
            End If
            If Not IsError(.Match(MyCellValue, Range("B2:B31"), 0)) Then
                Cells(i, j).Interior.ColorIndex = 40  'colors cell with this color if the value matches column B values
            End If
            If Not IsError(.Match(MyCellValue, Range("C2:C31"), 0)) Then
                Cells(i, j).Interior.ColorIndex = 42    'colors cell with this color if the value matches column C values
            End If
            If Not IsError(.Match(MyCellValue, Range("D2:D31"), 0)) Then
                Cells(i, j).Interior.ColorIndex = 44    'colors cell with this color if the value matches column D values
            End If
        End With
    Next j
Next i
End Sub

Hope that helps!