worksheet function - Excel Conditional Formatting based on Previous Cell

I would like to apply a formula to one column that outputs formatting based on what the cell before (cell to the left of cell in column 'F') it contains.

IF 'E' cell is GREATER THAN 10 AND 'F' cell is LESS THAN 11, highlight 'F' cell green

so far my formula is:

=AND($E9>10,$F9<11)

I'm applying this formula for the entire column but I want each individual cell to be based on the individual cell before it. I'm not sure if the '$' is messing things up or what but I can't make sense of the cells that excel is choosing to highlight.

I've looked at a bunch of other posts on here based on conditional formatting per previous cell and nothing has helped. Appreciate any assistance!

An example of what it's highlighting: (bold is highlighted (there's another style being applied in the 'E' column that styles numbers 1-10))

| E | F |

| 2 | 1 |

| 100 | 34 |

| 1 | 1 |

| 3 | 3 |

| 42 | 100 |

| 6 | 5 |

| 5 | 8 |

| 11 | 5 |

| 19 | 7 |

image of issue: http://imgur.com/a/fylSF

Answer

Your formula is essentially correct, so the issue may be that you are basing it on the wrong cell. Conditional Formatting formulas that are applied to a selection consisting of multiple cells should be based on the selection's Active Cell.

For example, if you want to apply Conditional Formatting to the entire column F, and you select that column by clicking on the column header, your sheet will look something like this:

In the screenshot above, the selection's Active Cell (the selected cell that is highlighted differently than all of the other selected cells) is cell F1. So, when you add a new Conditional Formatting Rule for this selection, just ensure that the formula is based on cell F1. Or, to put it another way, construct the formula as if cell F1 was the only cell being formatted:

=AND($E1>10,$F1<11)

Result: