I have a column of comments about certain Names in individual rows. I also
have a column with a list of just names. all of the names in column A are
random. the names in column b are in alpha order. I would like to create
either a conditional format or a formula to identify the names in a row..
without reading each row to locate the names.

Ex
a1 I love talking to jennifer but i also like working with Heidi.
a2 Justin answered all our questions before we had to ask.

b1 Michael
b2 Joan
b3 Jennifer
b4 Justin

Please help.

I have a column of comments about certain Names in individual rows. I
also have a column with a list of just names. all of the names in
column A are random. the names in column b are in alpha order. I
would like to create either a conditional format or a formula to
identify the names in a row.. without reading each row to locate the
names.

Ex
a1 I love talking to jennifer but i also like working with Heidi.
a2 Justin answered all our questions before we had to ask.

b1 Michael
b2 Joan
b3 Jennifer
b4 Justin

Maybe the following would help getting started.

In C1 make a pull-down list to choose one of the column-B names. To do
this, select C1 and use
Data Validation
and in the Settings tab
Allow: List
Source: =$B$1:$B$4
check "In-cell dropdown"

Then select A1 and use
Format Conditional formatting Formula Is
=NOT(ISERROR(SEARCH($C$1,A1)))
and choose a distinguishing format.

Then paste the format from A1 onto all of column A.

Then choosing a name in C1 should make the distinguishing format appear for
cells where the chosen name appears in column A.

Modify to suit.

(I have Excel 2003.)

Maybe i should clarify that column A contains comments - some with names and
some without.. so there are a lot of random words.. and I am only interested
in the cells that refer to someone's name. I am really trying to avoid
reading every cell.

Thanks.

"pomegranate-man" wrote:

I have a column of comments about certain Names in individual rows. I
also have a column with a list of just names. all of the names in
column A are random. the names in column b are in alpha order. I
would like to create either a conditional format or a formula to
identify the names in a row.. without reading each row to locate the
names.

Ex
a1 I love talking to jennifer but i also like working with Heidi.
a2 Justin answered all our questions before we had to ask.

b1 Michael
b2 Joan
b3 Jennifer
b4 Justin

Maybe the following would help getting started.

In C1 make a pull-down list to choose one of the column-B names. To do
this, select C1 and use
Data Validation
and in the Settings tab
Allow: List
Source: =$B$1:$B$4
check "In-cell dropdown"

Then select A1 and use
Format Conditional formatting Formula Is
=NOT(ISERROR(SEARCH($C$1,A1)))
and choose a distinguishing format.

Then paste the format from A1 onto all of column A.

Then choosing a name in C1 should make the distinguishing format appear for
cells where the chosen name appears in column A.

Modify to suit.

(I have Excel 2003.)