Conditional Formatting based on if specific text is present in range of cells

Question

Using Conditional Formatting, I cannot figure out the correct formula:

I would like cell G5 to have a red fill if the word "Cookie" appears anywhere in the range A16:A23

I have tried the following formula in Conditional Formatting but it will only work for the first cell in the range (A16)

=A16:A23="Cookie"

It is important to note that cells in the range A16:A23 are drop down boxes using data validation to a list on a separate worksheet in the same workbook.  I am not sure if this is affecting the results or not, but didn't want to leave out any relevant information.  Thanks!

Answer 1

Hi,

The conditional formatting formula should be:

=COUNTIF(A$16:A$23,"Cookie")>0

Hope this helps.

Answer 2

Hello this the formula for the conditional formatting, if it finds "Cookie" within any text in any cell will return TRUE and format the cell.

Just select the range and add a new rule with this FORMULA:

=ISNUMBER(FIND("Cookie",$A5))

Best regards!

Answer 3

Ashish:

This formula would only apply the conditional formatting if the word "cookie" was in the first entry in the range.  If it was any lower in the range, the conditional formatting would not apply.  Tried it out, and no good.

Mike

Answer 4

That is not correct.  It will work if Cookie is found anywhere in range A16:A23.  Please retry.