VBA to validate data

[jmthompson]

Hey guys,
I am building a workbook with lots of code that will allow the end user to paste data in a tab and generate reports based on that data. Sounds easy enough, but there are already a ton of macros and a couple of User forms to get from A to Z.

One of the first macros performs a fairly simple task, it validates some of the pasted data against some rules and highlights any violations. It's working fine, but now the end users have a new request. They want the macro to highlight any values in column A (excluding the header) that do not match members of a list on another tab.

This request has stumped me. One thought I had was for the code to perform a hidden vlookup for each value and if the result was "#N/A", then highlight the value, but I couldn't figure out how to perform the vlookup without putting it in a cell. I could do that, but it's extra steps and I'm trying to keep the code as clean as possible...for me.

Here is the vlookup snippet from my latest attempt, which errored out. Better ideas?

For Each c In Range("A2:A" & lastRow)
    If VLookup(c, "'Monthly 30lb Price Data'!A2:B100", 2, False) = "#N/A" Then
    c.Interior.Color = 65535
    End If
Next c

 

[VoG]

Hi. Try

For Each c In Range("A2:A" & lastRow)
    If IsError(Application.VLookup(c, Sheets("Monthly 30lb Price Data").Range("A2:B100"), 2, False)) Then
    c.Interior.Color = 65535
End If

 

[jmthompson]

Perfect! This is going in my codes to save folder

Hi. Try

For Each c In Range("A2:A" & lastRow)
    If IsError(Application.VLookup(c, Sheets("Monthly 30lb Price Data").Range("A2:B100"), 2, False)) Then
    c.Interior.Color = 65535
End If