Conditional formatting for cells in Col A based on text on another cell (N1, N2, N3)

[excelCM]

Hi,

Col A has 5000 data points, what I want to do is to format a few data points based on the words that I type in another cell(s), in this case N1, N2, N3. Here's an example:

Col A:

baseball bats
unique engagement ring
cement and concrete
build new business
analyze unique ring tones


Considering these are the 5 data points on Col A, if I were to type the word "unique" in cell N1, I would like the format of cell A2 and A5 to change to 'light green' AND if I typed "ring" in N2, then I would want the cell A2 and A5 to change to "dark green" and if I were to type "tones" on N3, I would want the cell A5 to be turned to another darker shade of green or whatever. How do I do it?

I tried the Conditional formatting > New rules > use a formula to determine which cells to format and then used the formula - "=isnumber(search($n$1,$a$1:$a$5))=true, but doesn't seem to work..

This formula works for a single cell (when I checked "engagement", a2) but when I select a range..it didn't. I would really appreciate your response..please let me know if you have any clarifications...Thanks!

 

[Andrew Poulsom]

Welcome to MrExcel.

With A1:A5 selected and A1 the active cell:

=ISNUMBER(SEARCH($N$1,A1))

 

[excelCM]

Welcome to MrExcel.

With A1:A5 selected and A1 the active cell:

=ISNUMBER(SEARCH($N$1,A1))

Dear Mr. Poulsom,

Thanks very much..the solution worked! (not on the same workbook though, so I copied and pasted the data on a new workbook and tried it)

Appreciate your help!

 

[Peter_SSs]

Welcome to the MrExcel board!

Since Conditional Formatting ignores errors, you could trim Andrew's suggestion to the following and it should do the same job.

=SEARCH($N$1,A1)

 

[excelCM]

Welcome to the MrExcel board!

Since Conditional Formatting ignores errors, you could trim Andrew's suggestion to the following and it should do the same job.

=SEARCH($N$1,A1)

...Sure, thanks very much!!