Excel formula

AQureshi1709 

You need to dissect a formula lik =SUM(IFERROR(FIND("Failed",$F$13:$F$53),0)) to understand what's going on. Start from the inside out and enter three formulas side by side

=FIND("Failed",$F$13:$F$53) looks for the word Failed in range F13:F53 and returns an array of errors and 1's . The 1's indicating the position(s) in the array where the word was found.

Add the IFERROR bit. Thus will replace the errors in the array with 0's .

=IFERROR(FIND("Failed",$F$13:$F$53),0) 

Finally, SUM that last array. If the sum = 0, it means that "Failed" was not found. Anything else indicates that it was found and you can use that to conditionally format the cell.

To avoid error messages when E6 is blank, wrap the formulas that produce the error in IFERROR, similar to what I described above. For instance =IFERROR(your_formula,"") to return an empty string is your_formula returns an error.

VBA is not my kind of thing. I stay away from it as much as I can.