Conditional formatting -Help needed

[VineetJ]

Hi,
I need to highlight the entire row in case a particular condition is true. I am using MS-Excel 2007 I could figure out, how to highlight a cell but I was unable to highlight the entire row, The formula I am using as a condition is : =OR(M3="Fail",W3="Fail",Z3="Fail",AG3="Fail",AJ3="Fail") and I want to highlight 3<SUP>rd</SUP> row in case the formula returns the ‘True’ value.
I would also like to apply the similar formula on the table from row 3 to row40. Can you please help me with the solution for this problem.

 

[Jonmo1]

Welcome to the board...

Change the formual to

=OR($M3="Fail",$W3="Fail",$Z3="Fail",$AG3="Fail",$AJ3="Fail")

And change the "Applies To" Range to $A$3:$AJ$40


Hope that helps.

 

[Andrew Poulsom]

Try this formula:

=OR($M3="Fail",$W3="Fail",$Z3="Fail",$AG3="Fail",$AJ3="Fail")

with the entire row selected.

 

[VineetJ]

Thanks Guys.. it worked, Can you please help me by explaining how the formula change from
=OR(M3="Fail",W3="Fail",Z3="Fail",AG3="Fail",AJ3="Fail")
to
=OR($M3="Fail",$W3="Fail",$Z3="Fail",$AG3="Fail",$AJ3="Fail")
Did the trick?
In short what is the significance of $..while mentioning the cell ranges?

 

[Jonmo1]

It's called absolute referencing...

the $ locks the column reference.
So when the formula is applied to subsequent cells to the right or left, the column reference stays the same...

=A1 changes to =B1, =C1, =D1 when formula dragged right.
=$A1 stays as =$A1 when formula is dragged right...

Similar for row reference..

=A1 changes to =A2, =A3, =A4 when formula dragged down
=A$1 stays as =A$1 when formula is dragged down

And you can use both as well

=$A$1 Stays $A$1 no matter which direction the formula is dragged.

Hope that helps.

 

[T. Valko]

Thanks Guys.. it worked, Can you please help me by explaining how the formula change from
=OR(M3="Fail",W3="Fail",Z3="Fail",AG3="Fail",AJ3="Fail")
to
=OR($M3="Fail",$W3="Fail",$Z3="Fail",$AG3="Fail",$AJ3="Fail")
Did the trick?
In short what is the significance of $..while mentioning the cell ranges?

A bit shorter:

=SEARCH("fail",$M3&$W3&$Z3&$AG3&$AJ3)