I have a table like this

|Physics| |Chemistry| |Math| |English| |Fail/Pass|

   50          30       60       25        F
   60          25       50       70        P

I want to highlight those cells which have F in their row and whose values are 1 < value < 35

For Example, first row has F in its Fail/Pass column. So all cells which have marks less than 35 and greater than 1 will be highlighted. In this case only Chemistry and English marks will be highlighted. Second row has P in its Fail/Pass column. So this row will be left untouched.

My attempts so far:

In Conditional Formatting Tab, I selected "Use a formula to determine which cells to format" but I am having trouble constructing a suitable formula.

I tried this one :

=AND(O6="F", OR(AND(D6>1, D6<35), AND(E6>1, E6<35), AND(F6>1, F6<35), AND(G6>1, G6<35)))

For some reason it only highlights the scores satisying all conditions in first column. This was my most successful formula (relatively speaking). Others were not impressive at all. Google only has results for highlighting entire row based on the content of a cell.

Any help will be appreciated. Thank you!


enter image description here

  • Select A151:E155.
  • Use this formula as New Rule: =AND($E151="F",A151>1,A151<35)
  • Apply an appropriate cell format.


  • To justify the formula, purposely I've put 25 in cell B152, since E152 has P.
  • You may adjust cell references in the formula as needed, also the marks to check.
  • Glad to help you @Pratik Haware,, keep asking ☺ May 2 '20 at 6:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.