Conditional Formatting with text

[birdec]

I have a list of first and last names in column A, some first names repeat.

I want to type in a first name in some cell (say H1) and all the cells in the list that have that same first name will be highlighted yellow.

 

[VoG]

Try Formula Is and the formula

=LEFT(A1,4)=H1

 

[birdec]

Try Formula Is and the formula

=LEFT(A1,4)=H1

Hmm didn't work. Neil is not the only name I want highlighted. Any first name that I type in H1 I would like highlighted.

If I type the full name in H1 I can do the EQUALS TO format but I'm stuck if I just want to type the first name.

 

[VoG]

You have to use the Formula Is option

Excel Workbook
	A	B	C	D	E	F	G	H
1	Neil Armstrong							Neil
2	Fred Scuttle
Sheet5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1	1. / Formula is =LEFT(A1,4)=H$1	Abc

 

[birdec]

Idk that didn't work. I wish I could insert an image here. What if the person's name was "Michael", you'd have to use =LEFT(A1,7)=H$1 wouldn't you?

 

[VoG]

Try

=LEFT(A1,FIND(" ",A1)-1)=H1

 

[birdec]

That works better!

But it usually gives me the name below the name I want
When I type in Kenya:

<table border="0" cellpadding="0" cellspacing="0" height="300" width="130"><colgroup><col style="mso-width-source:userset;mso-width-alt:4160;width:98pt" width="130"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;width:98pt" height="17" width="130">Student Name</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Tia Koth</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Clayton Bellis</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Ashlee Basch</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Hillary Kidney</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Neil Jahns</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Neil Hendryx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Guy Kosak</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Hillary Humiston</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Kenya Smoak</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none;border:.5pt solid windowtext; background:yellow;mso-pattern:black none" height="17">Darren Agena</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Karina Krejci</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Guy Lyda</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Javier Vey</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Roslyn Folkes</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Julianne Ranum</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Roslyn An</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Lance Guider</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Alejandra Bielecki</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Max Buckalew</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Tanisha Lorentz
</td> </tr> </tbody></table>
and when I type in Neil:

<table border="0" cellpadding="0" cellspacing="0" width="130"><colgroup><col style="mso-width-source:userset;mso-width-alt:4160;width:98pt" width="130"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;width:98pt" height="17" width="130">Student Name</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Tia Koth</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Clayton Bellis</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Ashlee Basch</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Hillary Kidney</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Neil Jahns</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none;border:.5pt solid windowtext; background:yellow;mso-pattern:black none" height="17">Neil Hendryx</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none;border:.5pt solid windowtext; background:yellow;mso-pattern:black none" height="17">Guy Kosak</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Hillary Humiston</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Kenya Smoak</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Darren Agena</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Karina Krejci</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Guy Lyda</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Javier Vey</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Roslyn Folkes</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Julianne Ranum</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Roslyn An</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Lance Guider</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Alejandra Bielecki</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Max Buckalew</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Tanisha Lorentz</td> </tr> </tbody></table>

 

[VoG]

I missed out a $. Try

Excel Workbook
	A	B	C	D	E	F	G	H
1	Student Name							Kenya
2	Tia Koth
3	Clayton Bellis
4	Ashlee Basch
5	Hillary Kidney
6	Neil Jahns
7	Neil Hendryx
8	Guy Kosak
9	Hillary Humiston
10	Kenya Smoak
11	Darren Agena
12	Karina Krejci
13	Guy Lyda
14	Javier Vey
15	Roslyn Folkes
16	Julianne Ranum
17	Roslyn An
18	Lance Guider
19	Alejandra Bielecki
20	Max Buckalew
21	Tanisha Lorentz
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =LEFT(A2,FIND(" ",A2)-1)=H$1	Abc