Highlight Cell above if cell below is Fail and 1

[thewok288]

Long time lurker and first time poster!

Hi all I have a spreadsheet of names and the data fields pass and fails (1 or blanks). I need a formula or Conditional formatting that if the fail is 1 to highlight the cell above. I'm trying to think of the best way to achieve this goal?

Thanks

 

[Rick Rothstein]

l I have a spreadsheet of names and the data fields pass and fails (1 or blanks). I need a formula or Conditional formatting that if the fail is 1 to highlight the cell above. I'm trying to think of the best way to achieve this goal?

Select the range you want to have this functionality, note the active cell... let's say for example purposes the cell is C8, use this conditional formatting formula...

=C9=1

In other words, test the cell underneath the active cell for being equal to 1.

 

[thewok288]

That works well Rick! Thank you very much. If i wanted to go one step further and have it only highlight the cell above if it had the word Fail in the row, could i use a IF formula?

 

[Rick Rothstein]

That works well Rick! Thank you very much. If i wanted to go one step further and have it only highlight the cell above if it had the word Fail in the row, could i use a IF formula?

I am about to go to sleep for the "night", so I won't be able to answer you for a few hours, but your question is not clear. Which cell has the word Fail in it... the one being colored yellow or the one you had me test for being equal to 1? If the latter, does that mean you are testing for either a 1 or the word Fail and coloring it the same color if either is in the cell?