I am using VLOOKUP to retrieve values from several different worksheets. I
also wish to retreive the cell's format (color). There are many different
colors on the worsheets based on many complex criteria. Is there anyway the
individual formats of the cells retrieved can also be returned with the
values ?

Thanks,
Rick

Not with a formula


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"rickcstahl" <(E-Mail Removed)> wrote in message
news:01086295-C9F8-49E6-B56A-(E-Mail Removed)...
>I am using VLOOKUP to retrieve values from several different worksheets. I
> also wish to retreive the cell's format (color). There are many different
> colors on the worsheets based on many complex criteria. Is there anyway
> the
> individual formats of the cells retrieved can also be returned with the
> values ?
>
> Thanks,
> Rick

you could use MATCH() instead to return the row/column in the table, then
use a range object to get that cell's properties



"rickcstahl" <(E-Mail Removed)> wrote in message
news:01086295-C9F8-49E6-B56A-(E-Mail Removed)...
> I am using VLOOKUP to retrieve values from several different worksheets.
> I
> also wish to retreive the cell's format (color). There are many different
> colors on the worsheets based on many complex criteria. Is there anyway
> the
> individual formats of the cells retrieved can also be returned with the
> values ?
>
> Thanks,
> Rick

i hit send too early
i was suggesting that MATCH would allow you to do this

example
so instead of
dim val as string
val = WorksheetFunction.VLOOKUP(what, where, ret, false)

dim val as Range
dim rowindex as long
rowindex = WorksheetFunction.MATCH(what, where.columns(1), false)
set val = where.resize(1,1).Cells(index,ret)






where: what is string to be found, where is the range table to be searched,
ret is the return column



"rickcstahl" <(E-Mail Removed)> wrote in message
news:01086295-C9F8-49E6-B56A-(E-Mail Removed)...
> I am using VLOOKUP to retrieve values from several different worksheets.
> I
> also wish to retreive the cell's format (color). There are many different
> colors on the worsheets based on many complex criteria. Is there anyway
> the
> individual formats of the cells retrieved can also be returned with the
> values ?
>
> Thanks,
> Rick