Conditional Formatting if another cell contains specific text

[JTL9161]

I am trying to highlight text in a cell if cell M4 contains the text "OFF"

I tried this but its highlighting regardless if the cell contains "OFF" or not.

=ISNUMBER(SEARCH("OFF",$M4))

Thanks
James

 

[rollis13]

Works fine for me, try again in a new clean sheet or workbook.

 

[etaf]

what text?
works for me

 

[DanteAmor]

I am trying to highlight text in a cell

Which cell?


Do you want to highlight the same "OFF" text that is inside cell M4?

 

[JTL9161]

If "off" is not there I want the font to say black. But it changes to red every time. ????

 

[etaf]

i used your formula
and applied to the range starting at M2

Book11
	M
2
3
4	off
5	m-off-1
6
7
8	off
9	m-off-1
10
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M2:M17	Expression	=ISNUMBER(SEARCH("OFF",$M2))	text	YES

 

[Peter_SSs]

FWIW you do not need that ISNUMBER function in your Conditional Formatting (though it shouldn't hurt).
As I understand the requirement, this is what you want. You existing formula, or this shorter one should do the same job.

23 12 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Hdr1	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13
2	data	data	data	data	data	data	data	data	data	data	data	data	OFF
3	data	data	data	data	data	data	data	data	data	data	data	data	ON
4	data	data	data	data	data	data	data	data	data	data	data	data	Neither
5	data	data	data	data	data	data	data	data	data	data	data	data
6	data	data	data	data	data	data	data	data	data	data	data	data	OFF
7	data	data	data	data	data	data	data	data	data	data	data	data	new office chair
CF OFF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:M7	Expression	=SEARCH("OFF",$M2)	text	NO