How to highlight a row containing specific phrase?

J

Josef Vosyka

For example I need to highlight all rows containing 'TODO' anywhere in the
text.
I've tried conditional formating with formula condition '=FIND("TODO";)>1'
but failed.
I'm looking for 2 answers:
1) how to represent the current cell reference in the formula. Is the simple
omitting (as shown above) correct?
2) how to apply the format to the whole row, not just to the cell meeting
the condition?

Thank in advance for your help!
--Josef
 
B

Barb Reinhardt

Try this condition:

=SEARCH("TODO",$A1)>0

$A1 is where the data is stored. You can select Column A,B, and C and enter
this and you'll get the entire row shaded if "TODO" is in A1.

If you want to look at data in multiple rows (say A-D), change the condition
to

=SEARCH("TODO",$A1&$B1&$C1&D1)>0
 
B

Bob Phillips

Use conditional formatting with a formula of

=SUMPRODUCT(--(ISNUMBER(FIND("TODO",1:1))))>0

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
J

Josef Vosyka

Hi!

thanks a lot. They both worked!

=SEARCH("TODO",$A1&$B1&$C1&D1)>0
=SUMPRODUCT(--(ISNUMBER(FIND("TODO",1:1))))>0

The second one does not need to specify the range and works with the entire
row.

I've got 3 more ?s if I may:

1) Is there a way to use the first 'SEARCH' approach and instead of
enumerating the columns just specify the entire column? Or at least an
interval, e.g. $A1:$H1. I've tried this, but it did not work.

2) Now if I need (for other purpose) to highlight only that single cell
containing the specific text, should not there be a simple 'cell value is'
condition with a wild chars e.g. '?TODO?'.

3) BTW, how does one escape the special char to be a 'question-mark' and not
the wild-char?

Thanks a lot for the second time!
--Josef
 
B

Bob Phillips

Josef Vosyka said:
Hi!

thanks a lot. They both worked!

=SEARCH("TODO",$A1&$B1&$C1&D1)>0
=SUMPRODUCT(--(ISNUMBER(FIND("TODO",1:1))))>0

The second one does not need to specify the range and works with the entire
row.

I've got 3 more ?s if I may:

1) Is there a way to use the first 'SEARCH' approach and instead of
enumerating the columns just specify the entire column? Or at least an
interval, e.g. $A1:$H1. I've tried this, but it did not work.
Click to expand...

Best formula, better than my first attempt is

=COUNTIF(1:1,"*TODO*")>0

1:1 can be a subset like A1:H1
2) Now if I need (for other purpose) to highlight only that single cell
containing the specific text, should not there be a simple 'cell value is'
condition with a wild chars e.g. '?TODO?'.
Click to expand...

Use your original approach, CF with a formula of

=FIND("TODO,A1)
3) BTW, how does one escape the special char to be a 'question-mark' and not
the wild-char?
Click to expand...

Put the quotation mark in another cell, and test against that.
 
J

Josef Vosyka

I do not wanna make this thread as long as the week before the pay-day, but
I've learnt some performace slow-downs with the use of COUNTIF and SUMPRODUCT
approach. The paging is slower and my PC is not exactly the slowest one.

However the SEARCH works great. How could one use it to highlight the entire
row if a value of any cell (given either by column enumeration or a subset)
equals exactly to '?' (question-mark sign).
 
B

Bob Phillips

You would need to build it into an array formula looking at all cells in the
row. That will make it far slower than COUNTIF or SUMPRODUCT.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

Josef,

I just timed it.

An array version using SEARCH like this

=COUNT(FIND("TODO",rng))>0

as against my non-array

=COUNTIF(rng,"*TODO*")>0

The array version was 6 times slower than my originla formula.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top