Conditional Formatting

[SMABonni]

Hello All,

I have a worksheet with 4522 rows and I would like to highlight some items that appear in both the worksheet and a list of 40 items. I have figured out how to highlight the first 3 values in the list with the conditional formatting options, but I am unable to figure out how to get all of the items on the list of 40 to be highlighted on the massive worksheet. I would appreciate any help you can give me.

Thanks,
Bonni

[SMABonni]

Hello All,

I have a worksheet with 4522 rows and I would like to highlight some items that appear in both the worksheet and a list of 40 items. I have figured out how to highlight the first 3 values in the list with the conditional formatting options, but I am unable to figure out how to get all of the items on the list of 40 to be highlighted on the massive worksheet. I would appreciate any help you can give me.

Thanks,
Bonni

[NBVC]

select the column/range you want to highlight

go to Format|Conditional Formatting

Select Formula Is from 1st drop down

enter formula: =Match(A1,X1:X40,0) where A1 is the top-left most cell in your selection and X1:X40 is your list of 40. Adjust as necessary.

Select your format and Click Ok to finish.

[NBVC]

Duplicate:

http://www.excelforum.com/showthread.php?t=607447

[SMABonni]

Thank you so much! It worked perfectly!!!

[NBVC]

Thank you so much! It worked perfectly!!!

Not sure if you caught it, but I did neglect to make the llokup array part of the formula absolute by adding the $-signs....

should be

=Match(A1,$X$1:$X$40,0)

this "freezes" the lookup array instead of offsetting.