Excel Conditional Formatting - Containing Multiple Matches

1
votes

Trying to avoid VBA and use one Conditional Formatting rule.

I want to apply a format to cells that contain one or more possible phrases.

   |   A    |
---+--------+---
 1 | foo    |
 2 | bar    |
 3 | foobar |
 4 | baz    |
 5 | foobaz |
 6 | qux    |
 7 | barqux |

Goal: apply conditional format to 'foo' and 'baz' (rows 1,3,4,5)

Something like the following should work, however I think the problem is in identifying the current cell to compare, in this case the A1:A7 needs to be the current cell reference:

=OR( IFERROR(FIND("foo",A1:A7),0) > 0, IFERROR(FIND("baz",A1:A7),0) > 0 )

Any ideas?

2
excelconditional-formatting

2 Answers

2
votes

I found the answer.

Even though the conditional format was being applied to the range $A$1:$A$7, all you need to do is refer to the first cell in the function.

=OR( IFERROR(FIND("foo",A1),0) > 0, IFERROR(FIND("baz",A1),0) > 0 )

It'll automatically increment so long as you don't use the lock operator ($). - Sorry if this is common knowledge. I'm going to keep this here for educational purpose, if no one has a problem.

1
votes

You're perfectly right. You have to enter the reference to the "selected" cell. E.g. if you click into cell A1 and then drag and select cells A1..A7 you will see that A1 has different background than the other cells. This is your current cell for the conditional formula. I.e.

=OR( IFERROR(FIND("foo",A1),0) > 0, IFERROR(FIND("baz",A1),0) > 0 )

would be correct in this case. For the other cells the formula will then be adjusted accordingly.