Searching text in a cell range

[Guest]

hi there,

i have a problem with Excel. can anyone tell me how can I search a text of
cell in sheet2 in the cell range of sheet1.

suppose that the data in cell A5 of Sheet2 is Scott, and on Sheet1 coulmn D
have a list of names, how can I find the Scott in this case. Also I clear one
thing the list of names are unique there is no duplications in it. so there
will be one and only one Scott.

 

[Frank Kabel]

Hi
what do you want to do with the '*found value'

--
Regards
Frank Kabel
Frankfurt, Germany
"ShareerIslamabadiMunda" <[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[Guest]

i want the found value to be in changed color like red.

 

[Guest]

I want to change the color of found text in Sheet1. Like if A name "Scott" is
founded in Sheet1 then its color in sheet 1 will be RED

 

[Jason Morin]

1. Select A5 on Sheet2.
2. Give it a name (Ctrl+F3) like "myname" (no quotes).
3. Select col. D of Sheet1.
4. Go to Format > Conditional Formatting
5. Select "Formula Is" and put:
=COUNTIF($D1,"*"&myname&"*")
6. Press the Format button and format as desired.

HTH
Jason
Atlanta, GA

 

[Guest]

Sorry but it is not working. OK Look at this if it could be

Sheet1 have 2000 names list, and sheet2 contains 877 names of sheet1. I want
the rest names in sheet one deleted which are not in Sheet2.

can it be

 

[Frank Kabel]

Hi
- define a name for D1100 on sheet 1 (menu 'Insert - Name - Define). e.g.
call this name 'sourcelist'
- select A5 on sheet 2
- goto 'Format - Conditional Format'
- enter the following formula
=COUNTIF(sourcelist,A5)
- choose your format

 

[Guest]

thanks Frank Kabel thanks man it realy very helpful for me. you helps a lot.
thanks again.

Saddat Sarfraz
Saadi