partial word match for conditional formatting

[Maureen]

Is it possible to do a partial word match for conditional formatting.
I want to search for any cell that contains the word 'listen' in either
upper or lower case and the cell may contain other characters. I tried =
*listen* but it didn't work.

If possible, I'd also like to change the color of the whole row not just the
matching cell in the row.

 

[T. Valko]

Try using the Formula Is option and use this formula:

=SEARCH("listen",cell_ref)

If you want to highlight the entire row make the cell_ref absolute:

=SEARCH("listen",$A1)

 

[Maureen]

I found a partial fix for my problem. Rather than conditional formatting, I
did a find/replace(under Edit-Replace on menu). Under 'Find What', I entered
'listen'. Under 'Replace With', I left the box empty but clicked on the
format button where you can change the font or cell (pattern) color.
That enabled me to change the cell. If possibly, I'd like to change the
color of the whole row not just the cell.

 

[TomPl]

When setting up conditional formatting select "Formula Is" then enter this
formula:

=SEARCH("listen",A1)>0

The cell I referenced (A1) is the same cell that is being formatted.

You could affect the whole row by putting this conditional formating in the
whole row but always reference cell (A1) or whatever cell you decide is the
key.

Hope this works for you.

Tom

 

[Maureen]

So, I'm not quite sure how to make that work on the whole worksheet or format
the whole row.
I can add another column that uses =SEARCH("listen",A1)>0, copy the formula
all the way down the worksheet, then do a conditional format for the value
"TRUE" but it only applies the conditional format color on the cell
containing the word 'TRUE' not the whole row.
If it try to do conditional format on a range ie.
=SEARCH("listen",A1:I5000)>0, I get odd results.
Column I is the column that may contain the word 'listen'. If it finds
'listen', I want the row A15:I15 to be highlighted.

 

[TomPl]

Try this:

Select cell A1.
From the menu select <Format><Conditional Formatting>
Change drop down to "Formula Is"
Enter this formula =SEARCH("listen",$A$1)>0
Select the format you want
Select OK

Test this by typeing "listen" in cell A1 to make sure you get your desired
format.

Next:
Select cell A1.
Copy
Select all the cells you want to change format when A1 contains "listen"
Paste Speccial, Formats

All of the cells you pasted that format to should now change format based on
the content of cell A1.

I hope that is clear. Communication can be challenging.

Tom

 

[Maureen]

Thanks, that gives same effect as find/replace with format - highlights
matching cell as expected.
I would guess there isn't a way to highlight a whole row based on a match in
1 column.

 

[T. Valko]

Try this...

Assume you want to highlight rows where any cell in the range A1:I10
contains the word "listen"...

Select the range A1:I10
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the box on the right:

=COUNTIF($A1:$I1,"*listen*")

Click the Format button
Select the desired style(s)
OK out

 

[TomPl]

You could paste the format to the whole row and the whole row would be
highlighted.

 

[Spiky]

Let's tweak Tom's steps a little:

Select range A1:G1.
From the menu select <Format><Conditional Formatting>
Change drop down to "Formula Is"
Enter this formula =SEARCH("listen",$A1)>0
Select the format you want
Select OK
--note my changes in line 1 and 4.

Next:
Select range A1:G1.
Copy
Select all the cells you want to change format when Column A contains
"listen" (so maybe A1:G300)
Paste Speccial, Formats
--note my changes in line 1 and 3.