format partial row based on cell text

Question

I am currently using Excel MS Office 2007 and trying to change a range of rows  to red if there is "fail" in the criteria ranging from f4:n5000

then all the other rows to be yellow if "fail" is not present.  also would like the font color to be white on fail only. I do have a conditional rule for that, but dont know if it will effect anything.

Not sure if it possible but would like to automatically copy failed row to another sheet. KINDA like this.

551	20-Jun									PASS	PASS	PASS	NA
552	20-Jun									PASS	PASS	PASS	NA
553	20-Jun									PASS	PASS	PASS	NA
554	20-Jun									PASS	PASS	FAIL	NA
555	20-Jun									PASS	PASS	PASS	NA
556	20-Jun									PASS	PASS	PASS	NA

Answer 1

Hi,

• Highlight your range / press F5 (function5) then enter "F4:N500" (no quotes) in Reference
• Press Alt+O then release Alt and press D
• In Conditional Formating manager dialog box, click NEW RULE
• Select Use a formula to determine which cells to format (in the bottom of rule type)
• Enter this formula in Format values where this formula is true text box

(With FAIL found)

=COUNTIF($F4:$N4,"Fail")>0

• Click format, then in fill tab select your fill color / Font Tab for Font color
• ok
• ok
• ok

Repeat Steps for no fail found then use this formula

=AND($F4>0,COUNTIF($F4:$N4,"Fail")=0)

Hope it helps,

~jaeson