June 27, 2016
I have a formula =SEARCH($AY$3,AY6,1)>0 in conditional formatting, which says if the string in AY6, is found in a range, turn any of the cells that contain
that string to red. Is it possible to change the formula, to look for more than one string.
VIP
Trusted Members
June 25, 2016
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
Hi Tim,
Use a table with multiple values to search, this formula for conditional formatting will do the job:
=SUM(IFERROR(SEARCH(StringsToSearch,A2),0))>0
Where StringsToSearch is a defined name, for 1 or more cells (unlimited number of cells) containing your text to search.
By default, in named ranges or in conditional formatting rules, these formulas are treated like array formulas, so they can be designed as array formulas, but they do not have to be entered with CSE like worksheet cell array formulas.
1 Guest(s)