If a list of cell Values do not match a value within a range, highlight the cell

[APInfa]

I am creating a system which reads all existing unit of measurements (UOM) and checks the data I am trying to enter. If the new data has a UOM that doesn't match I would like to highlight it. Here is the code I am using but it's giving me a mismatch error on my main "if" statement:

Sub Match()

Dim cells1 As Range
Dim cell As Range
Set cells1 = Range("D3", "D1000") ' Data I am trying to check
Dim Check As Range
Set Check = ThisWorkbook.Worksheets("Sample Code (Don't Alter)").Range("D7", "D1000") 'UOM Data Storage

'Loop through each cell in your selected range looking for highlight
For Each cell In cells1


'will highlight the cells that don't match the existing UOM interface
If Not cells1.Value = Check.Value Then
cell.Interior.ColorIndex = 20 'HERE IS THE MISMATCH ERROR

End If

Next cell
MsgBox "All UOM That don't match have been marked"

End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub APInfa()
   Dim Cl As Range
   Dim Dic As Object
   
   With CreateObject("Scripting.dictionary")
      For Each Cl In Sheets("Sample Code (Don't Alter)").Range("D7:D1000")
         If Cl.Value <> "" Then .Item(Cl.Value) = Empty
      Next Cl
      For Each Cl In ActiveSheet.Range("D3:D1000")
         If Cl.Value <> "" And Not .Exists(Cl.Value) Then
            Cl.Interior.ColorIndex = 20
         End If
      Next Cl
   End With
End Sub

 

[Peter_SSs]

What about using Conditional Formatting?

Sub HighlightNonMatches()
  With Range("D3:D1000")
    .FormatConditions.Add Type:=xlExpression, Formula1:="=AND(D3<>"""",ISNA(MATCH(D3,'Sample Code (Don''t Alter)'!D$7:D$1000,0)))"
    .FormatConditions(1).Interior.ColorIndex = 20
  End With
End Sub