VBA using VLookUp
 

I have two workbooks open: book2.xls and Dept_IDs.xls

I want to use VBA to determine if a value in cell A7 of Sheet1 of book2.xls
needs to have its cell colored based on the 3rd column of data in a range
named: Dept_ID_T in the workbook: Dept_IDs.

IE, if the
VLookUp([book2.xls]Sheet1!A7,[Dept_IDs_0911.xls]Dept_ID_T_WS!Dept_ID_T,3,FALSE) = "410-50", then I want to color cell A7 a light yellow.

What's the VBA to determine if the vLookUp would return the value "410-50"?

Your help will be appreciated.

Thanks.

VBA using VLookUp
 

Try this. I used FIND instead of VLOOKUP. I also set variables for each
item to make it easier to understand the code and to debug the code.

Set bk = thisworkbook
Set sht = bk.sheets("Sheet1")

Set Deptbk = workbooks("Dept_IDs_0911.xls")
set DeptSht = Deptbk.sheets("Dept_ID_T_WS")

LookupValue = sht.range("A7")
set c = DeptSht.Range(Dept_ID_T).find(what:=LookupValue, _
lookin:=xlvalues,lookat:=xlwhole)
if c is nothing then
msgbox("did not find : " & Lookupvalue)
else
'get column 3 of table
Col_3 = c.offset(0,2)
if Col_3.value = "410-50" then
sht.range("A7").interior.colorindex = 6
end if
end if


"ND Pard" wrote:

I have two workbooks open: book2.xls and Dept_IDs.xls

I want to use VBA to determine if a value in cell A7 of Sheet1 of book2.xls
needs to have its cell colored based on the 3rd column of data in a range
named: Dept_ID_T in the workbook: Dept_IDs.

IE, if the
VLookUp([book2.xls]Sheet1!A7,[Dept_IDs_0911.xls]Dept_ID_T_WS!Dept_ID_T,3,FALSE) = "410-50", then I want to color cell A7 a light yellow.

What's the VBA to determine if the vLookUp would return the value "410-50"?

Your help will be appreciated.

Thanks.