Validation ON List of values against by command button

[Guest]

Thank you in advance

I need a command button to check if a list of values I input actually are in
another list on a different worksheet. If they are not in the list it would
be highlighted a color. I understand there are easier ways (conditional
formatting, drop down, etc) but my boss wants it done specifically this way.
Please help

-Thank you, Alex

 

[JW]

Something like this. Of course, you would programatically create your
ranges dimensions if they are subject to change.
Dim cl As Range, enteredList As Range, compareList As Range
Set enteredList = Range("A2:A15")
Set compareList = Range("C2:C15")
For Each cl In enteredList
If WorksheetFunction.CountIf(compareList, cl.Value) = 0 Then
MsgBox "not in list"
Else
MsgBox "in list"
End If
Next cl
Set entereList = Nothing
Set compareList = Nothing

 

[Guest]

Thank you. The Compare list is in a different worksheet though. I tried
using =Range("CC!A2:A15"), however that doesn't work. Also instead of a
message I would like the cells to be higlighted if they are not in the list.

-Thank you, again

 

[Guest]

I've actually got the highlight part down, but still need help in set
comparelist on a different worksheet. Also, is there a way not to specify a
range. Because both the entered and compare list will always be different.
In other words have it select the range based if the value is not null.

 

[JW]

To reference the range in a seperate workbook, use something like
Worksheets("CC").range("A2:A15")

As for not setting it at all, the program wouldn't know where to look
for the compare. Are the lists always in the same columns? If so, we
can automate the range dimension by finding the top row and bottom row
of the columns.

Just need a little more detail.

 

[Guest]

Yes both lists are in the same column in their respective worksheets. I was
fooling around with this piece of code; Range("B3").End(xlDown).Select. but
nothing Yet. The Entered list is in Worksheet Orginal starting in cell B3
and the Compare List in Worksheet CC starting in A1

 

[JW]

Not tested, but this should do the trick. Could be streamlined a bit
more, but you get the idea.
Dim cl As Range, enteredList As Range, compareList As Range
Dim enteredBotRow as Long, compareBotRow as Long
enteredBotRow = Range("B65536").End(xlUp).Row
Set enteredList = Range("B3:B" & enteredBotRow)
compareBotRow = Worksheets("CC").Range("A65536").End(xlUp).Row
Set compareList = Worksheets("CC").Range("A1:A" & compareBotRow)
For Each cl In enteredList
If WorksheetFunction.CountIf(compareList, cl.Value) = 0 Then
MsgBox "not in list"
Else
MsgBox "in list"
End If
Next cl
Set entereList = Nothing
Set compareList = Nothing