Multiple conditional formating for one cell

  • Thread starter Thread starter tech1NJ
  • Start date Start date
T

tech1NJ

I have a workbook with several sheets. Sheet1 has several hundred rows of
text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2
I have I have a cell that allows my users to free hand type in a keyword(s)
to search for in Sheet1. This cell is also has a reference name of
"Special_Char".

Now comes the question..... I like to for the user to type the keywork into
"Special_Char" and any or all the rows in Sheet1 that contain the keyword as
part of its text to turn yellow. Is there a way to do this? Please keep in
mind that the keywork is only part of the words in each of the cell. Your
help is appreciated in advance.
 
tech1NJ

I would use a column (A say) on Sheet1 to search for your user's keyword in
your text, row by row

=IFERROR(FIND(special_char,B16),FALSE)

(text here assumed to be in column B). Column B is then conditionally
formatted according to the formula in $A16 etc. A number (string found) rates
as TRUE for the test, not found gives an error that is then replaced by
FALSE. Finally you can 'hide' column A by reducing its width to zero. If
you have more keywords use extra columns to search one by one and combine the
boolean results with an =OR(A16,B16,C16) to determine the conditional
formatting to use.

I hope this helps
 
Back
Top