Highlight Row if Any Cell Contains Phrase

[crlyn]

Hi there!
I've searched all over for this and can only find examples specific to the asker's spreadsheet, and I can't figure out how to make the answers apply to my situation, so I figured I'd ask here!

The Problem:
I have a couple of spreadsheets I'm creating for a small group to use, and I would like to highlight an entire row if they enter anything that includes a certain phrase (like "paypal" or "wants"). I need these to be not case-sensitive, and conditionally format the colors and borders settings. I messed around with search formulas in conditional formatting, but couldn't get it to take...either I wasn't doing it right, or I need to use a VBA or something (which I don't know how to write).

For example, someone might write "$500 PayPal Deposit" in C2 or N2, and I'd like to highlight that entire row.

Luxury request: Being able to merge all the cells from A to O for that row, so that the deposit phrase is center aligned in the entire chart. I don't even know if this is possible, but that would be really nice.

Thanks so much for any help!

 

[VoG]

See if this helps http://www.contextures.com/xlCondFormat02.html

Avoid merged cells - they are the work of the

 

[crlyn]

Thanks for the quick response, VoG! However, that link doesn't help at all.

I need to find cells with a specific phrase of text in them, so filtering for "$C2<X" or "$C2>X" or "$C2=X", anything like that, won't work (meaning 99% of the "conditional formatting help" threads and blog posts don't help me).

 

[VoG]

Maybe like this or you''ll have to be more specific

Excel Workbook
	A	B	C	D	E	F	G	H
2	x	x	$500 PayPal Deposit	c	c	c	c	c
Sheet7

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =ISNUMBER(SEARCH("PayPal",$C2))	Abc

 

[crlyn]

We're getting closer! What you posted is exactly as far as I got and is exactly the basic idea of what I'm trying to do.

What I want that's beyond this is for the entire row to be highlighted, and I can't make that happen by either applying the rule to the selected sheet or the selected first row (row 2).

Also, it would be nice to have this happen if someone puts the info in a different spot, too. (Like in column B or column N or somewhere because they're not reading directions or whatever. )

Thanks for your help!

 

[VoG]

Let's stick to a value in column C, at least to start with.

Select A2 to as far right and down as far as you want to go then apply the CF formula that I posted.

 

[crlyn]

Oh beautiful–that worked! And combined with a "$L2>0" rule means that either of the two logical places on my sheet (note mentioning 'paypal' or amount of money deposited) makes it light up the entire row.

If we (and by "we" I mean you or someone else giving ideas and me plugging them in and trying to get them to work. hah) can figure out a way to make this work for any cell in the line, that would help for another spreadsheet I'm doing, but I can also just duplicate the rule you gave me for a handful of columns and have almost the same effect.

Thank you! (And for letting me get this thing working today, no less. )

 

[VoG]

For any cell in the row

=COUNTIF($A2:$N2,"*PayPal*")>0

 

[crlyn]

That highlights the row directly above the cell filled. Odd.

 

[VoG]

Select A2 to N as far down as you need then apply the CF

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2	49	53	$500 PayPal Deposit	58	7	11	19	55	37	100	95	8	62	13
3	85	52	46	62	49	48	89	79	24	99	92	15	43	80
4	78	23	11	73	16	10	49	75	40	94	84	1	$500 PayPal Deposit	27
5	7	65	70	65	68	6	61	52	79	67	8	71	85	21
Sheet2

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =COUNTIF($A2:$N2,"*PayPal*")>0	Abc