need help from Italy

[nicitalia]

Hi everybody,

I just joined the forum, mi name in Nic and I come from Italy.

Here's my question, hope someone could help me!

I got a range("A1:A20") in sheet2, each cell has a value of this kind: 50.20.00 P, 49.13.00 P and so on...for a total of 20 different values.

Now I want to search for these values in in sheet1.

I want do that with a macro but not writing a thousand if,
so I was thinking if ther's a way to create an array which contains the 20 differnt values (eg. I want the the instructions that read the values in the cells A1:A20 and create an array whit these values).
Then the macro will search for the values (contained in the array) in sheet1.range("A2:A1700"), if the value is found i want that the entirerow would be filled with yellow.

Hope you'll understand, sorry for my english
please help me!!!

thanks a milion

 

[GlennUK]

You don't need a macro, you can do this with a formula test and Conditional Formatting.

In Sheet1 select cell A2, and do menu command Insert/Name/Define, and create a name of test, with a definition of:

=NOT(ISNA(MATCH(Sheet1!$A2,Sheet2!$A$1:$A$20,0)))

( this may be slightly different for you, because of language differences )

Now, select A2:X1700 ( or however far across you want to do formatting, and do menu command Format/Conditional Formatting, and choose, Formula Is, with a formula of:
=test
and press the Format... button to choose a Pattern fill of yellow.

That should do it.

Cheers, Glenn.

Beauty is in the eye of the beerholder.