Conditional formatting triggered by multiple text options in a different cell

Question

I'd appreciate any help I can get.

I have a worksheet that people will fill out with different codes.

I want a cell (F5) to appear to be blank (font color is white) unless another cell (or groups of cells) contains a certain code.

If they do, then the font in F5 will be black.

The problem I have is making a rule that will cover the different variations of the "trigger" code, which could be three different codes, in either uppercase or lowercase.

Is there a way to assign multiple conditions to a cell?

Right now, my formula is (F5-font is black) =$K$16="33elect0"

is there a way to make it (F5-font is black) if K16 contains "elect" (regardless of case)?

Or (F5-font is black) if K16 contains "elect" OR "util" OR "prop" (also regardless of upper or lower case?

Or will I have to enter the exact code and its variant?

Rule 1 (F5-font is black) =$K$16="33elect0"

Rule 2 (F5-font is black) =$K$16="33ELECT0"

Rule 3 (F5-font is black) =$K$16="33util0"

Rule 4 (F5-font is black) =$K$16="33UTIL0"

and so on...

And I hope to copy this rule for cells K19, K22, K25 etc.

I hope this makes some kind of sense.

Answer 1

Hi,

Try this

1. Click on cell K16 and go to Home > Conditional formatting > New Rule > Use a formula to determine which cells to format
2. In the Formula bar there, enter this formula

=OR(ISNUMBER(SEARCH("elect",K16),ISNUMBER(SEARCH("util",K16)),ISNUMBER(SEARCH("prop",K16)))

3. Click on Format and choose your desired formatting
4. Click on OK/Apply

You may now copy this formatting to other cells.

Hope this helps.

Answer 2

Untested rambling:

First, consider using UPPER on the cell value to reduce the number of formulas.

Next, use FIND to see if your substring ("elect", "util", etc) is present. If not it will return an error, so use

ISERROR to check.

ISERROR will actually give you the opposite of what you want, a value if it is an error, and FALSE (zero) if it isn't an error, so I think (again, untested) you could do something like:

=(ISERROR(find("ELECT",UPPER(K16)))*1)*(ISERROR(find("UTIL",UPPER(K16)))*1)*(etc)

If my logic is right, then if any of those strings are found, one of the values will be zero, so the whole string will be zero. So in your conditional formatting dialogue (use formula)

=0=(Iserror....

Even if that doesn't work exactly, hopefully it gets you close enough to figure it out. If it doesn't work, try each piece of the formula individually so you can see the results in a cell, then keep putting the smaller pieces together until you have a cell formula that returns a zero or one, then stick that in your conditional formatting formula box

You could also use an array formula to check each of your keywords and only have one formula, but I suggest understanding how the non-array version works first- otherwise you'd have a harder time troubleshooting the array version