Prev: Excel 2003 - macro options grayed-out
Next: Restrict Pivot Tables
From: ND Pard on 12 Oct 2009 11:59
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.
From: Joel on 12 Oct 2009 12:16
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.
 | 
Pages: 1
Prev: Excel 2003 - macro options grayed-out
Next: Restrict Pivot Tables