I have a two sheet workbook. Sheet one contains a list of parts. Sheet two
contains two columns, A and B, with a generic part name and the status of
that part. Example:

Sheet 1
A B
Rubber Hose Low Stock
Garden Hose In Stock

On sheet Two I have:
A B
Hose In Stock

I'm using the following to conditionally format "Low Stock" to change the
background to red:

=VLOOKUP(B4:G104,myRange2,2,0)="Low Stock"

So far, this works great if the cell contains the value "Hose" only. But if
there's anything infront of the word Hose such as "Garden Hose" it won't
work. I've attempted the following:

=VLOOKUP("*"&B4:G104,myRange2,2,0)="Low Stock"

But it's not Concatenating properly.

I've also got the issue that the cells on sheet 1 contain an "Alt+Enter" for
multi-line cells. However, I'm willing to change the format if this is going
to pose a problem.

Any help would be appreciated.

=VLOOKUP("*"&B4:G104,myRange2,2,0)="Low Stock"

The lookup_value has to be a single reference, not an array:

=VLOOKUP("*"&B4,myRange2,2,0)="Low Stock"

Or:

=VLOOKUP("*"&B4&"*",myRange2,2,0)="Low Stock"

--
Biff
Microsoft Excel MVP


"Dmox" wrote in message
...
I have a two sheet workbook. Sheet one contains a list of parts. Sheet
two
contains two columns, A and B, with a generic part name and the status of
that part. Example:

Sheet 1
A B
Rubber Hose Low Stock
Garden Hose In Stock

On sheet Two I have:
A B
Hose In Stock

I'm using the following to conditionally format "Low Stock" to change the
background to red:

=VLOOKUP(B4:G104,myRange2,2,0)="Low Stock"

So far, this works great if the cell contains the value "Hose" only. But
if
there's anything infront of the word Hose such as "Garden Hose" it won't
work. I've attempted the following:

=VLOOKUP("*"&B4:G104,myRange2,2,0)="Low Stock"

But it's not Concatenating properly.

I've also got the issue that the cells on sheet 1 contain an "Alt+Enter"
for
multi-line cells. However, I'm willing to change the format if this is
going
to pose a problem.

Any help would be appreciated.