Using conditional formatting to highlight the row of a cell containing specific text.

[Rikaaay]

Hi, as mentioned in the title, I'm trying to make a conditional formatting rule where if a cell contains a certain word (potentially in amongst other text in the same cell), the whole row of that cell will be highlighted. Unfortunately I cant quite get the formula right, so looking for suggestions (using excel web).

As an example, imagine that this is the table, and the word I want to find is "word".

This is the type of formula that I've been trying.

However that highlights the whole range instead of just the relevant row (I am trying to avoid having a separate rule for every row), and it ends up like this.

I would like it to show like this (see below) for any row containing "word".

Does anyone have any suggestions for a formula that would work?

 

[etaf]

=ISNUMBER(SEARCH("word",cell to test,1))

in this example using column G as having "word"

for 2007, 2010 , 2013 , 2016 , 2019 or 365 Subscription excel version
Conditional Formatting

Highlight applicable range >>
A2:D100 - Change, reduce or extend the rows to meet your data range of rows

Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:
=ISNUMBER(SEARCH("word",$D2,1))

Format [Number, Font, Border, Fill]
choose the format you would like to apply when the condition is true
OK >> OK

Book3
	A	B	C	D
1
2
3				this is word form
4
5				we have a word with all
6
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:D7	Expression	=ISNUMBER(SEARCH("word",$D2,1))	text	YES

 

[Fluff]

You need to remove the $ sign from between the D & the 2 so it's $D2

 

[Rikaaay]

Ah perfect, I'd tried removing the $from between the D & 2 with no luck, but I just needed the ", 1" adding on the end and is working now. Thanks all

 

[etaf]

glad it worked - BUT that surprised me , as Fluff suggestion should have worked without the 1

Start_num: is the character number in Within_text, counting from the left, at which you want to start searching. If omitted, 1 is used.