Data Validation

[Guest]

As part of a procedure, I need to check values against those in another
spreadsheet. How do I check values against a range?

For example:

If basebook.Worksheets(1).Cells(rnum, cnum).Value 'is contained within the
range
SiteBook.Worksheets(1).Range("a3:a87") Then

colour the cell green
else colour the cell red

Built in conditional formatting on a list wont work here, as this needs to
run as a procedure is pulling data into the workbook from another (third)
book.

I want to replicate excels data validation on a list using vba, but just
can't get my head around the look up and return true/false bit. Can anyone
offer a suggestion?

Thanks for your time.

 

[Guest]

I presume you've identified basebook and sitebook in your code.

Try something like this:

dim r as range

basebook.Worksheets(1).Cells(rnum, cnum).interior.colorindex = 3
for each r in SiteBook.Worksheets(1).Range("a3:a87")
if r.value = basebook.Worksheets(1).Cells(rnum, cnum).value then
basebook.Worksheets(1).Cells(rnum, cnum).interior.colorindex = 4
exit for
end if
next r

 

[Guest]

Thanks Barb, I've been struggling with this for a while and your solution has
worked perfectly.