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!