Conditional Formatting Formula

[spr1nger]

What is the formula to use in conditional formatting, if I want to highlight cells A2:E2, if E2 has an "O" in it?

SC DOCUMENT Matrix.xlsx
	E
2	O
Purchasing

Cell Formulas
Range	Formula
E2	E2	=VLOOKUP(B2,'Doc Matrix'!B2:R195,17,FALSE)

 

[Joe4]

Select your range A2:E2, then go to Conditional Formatting and enter this formula:

=$E2="O"

and then select your desired formatting.

Note that the location of that "$" is critical. That locks it down so that ALL cells in your selected range look at column E.

 

[spr1nger]

Thank you. I was thinking I had to use an IF function so I was over complicating it. That worked, thank you!

 

[Joe4]

Thank you. I was thinking I had to use an IF function so I was over complicating it. That worked, thank you!

You are welcome.

No, you don't need to use an IF function since in your Conditional Formatting formulas, you need to return Boolean values (TRUE or FALSE).
So you can simply use equations that return TRUE or FALSE (i.e. by using a formula that sees if two things are equal or not).
You typically see a lot of Conditional Formatting formulas structured like this, or use the inequality functions like > or <, or one that use AND or OR functions, if you have multiple things you want to check.

For example, if you wanted to only highlight rows where column E was "O" and column F was greater than 100, you could do:

=AND($E2="O",$F2>100)