Validate Cell Value Against Range Or Array

I would like to use VBA to validate the data in an Excel worksheet against either a named range or an array of valid data.

The valid data comes from an accounting system. It is a list of customer numbers. I am using ODBC and SQL to get the data into an array and then into a named range. This is working fine.

I want to validate the customer number column in my source Excel worksheet against either the array or the named range.

There are a variable number of lines in the source data.

I have a VBA procedure which starts at cell A2 and then works down column A until it reaches a blank cell.

I would like to use a VBA formulae which does something like:

IF active cell offset (0,3).Value in (named range or array)
Then set cell colour for active cell offset (0,3) = Green
Else set cell colour for active cell offset (0,3) = Red
End If

Or something like that.

Can this be done using VBA? Or do I need to create a new column in my source data and use VBA to place a VLOOKUP based on a named range into this new column?