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:


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

  • What should it highlight instead? "before" - do you mean above or to the left? You don't have any lines where E>10 and F<11, so it's difficult to understand your example. Please edit your question and clarify these point, also please upload a screenshot from your rule and current status (as you can't yet post pictures, just upload it to imgur.com and include its link in your question). – Máté Juhász Dec 15 '16 at 18:30

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:

enter image description here

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:



enter image description here

  • Thanks for your assistance. I believe I'm doing what you're saying, I'm selecting the entire column and I start it at E4 because that's where the data starts. I have titles and headings in E1,2, and 3. Here's a screenshot to what my set up looks like imgur.com/a/mzefd – oosmoos Dec 15 '16 at 20:15
  • I don't think you are. Where the data starts is irrelevant. Your formula must be based on the Active Cell. Again, if you are selecting the entire column by clicking on the column header, the Active Cell will be the first cell in the column, which in your case would be F1. So, you should use the formula I provided in my Answer. If you are not selecting the entire column, the Active Cell will be the first cell you click on to start the selection, in which case your formula should refer to that cell (and the cell to its left). – MJH Dec 15 '16 at 20:32
  • You're a wizard! It worked! I just didn't understand your first explanation but it makes sense. Thank you very much!! – oosmoos Dec 15 '16 at 20:47

If you are wanting to use conditional formatting on the F column while checking both the F and the E Column, use the name manager. With the cursor on F9, go to the ribbon/formula and open the name manager Make a name called Flag and enter in your formula

Flag equation

Then in conditional formatting for the F column, select use a formula and then the formula is =Flag

enter image description here

  • Thank you for your response, that didn't work either. Here's what it did instead: imgur.com/a/fIrMP – oosmoos Dec 15 '16 at 20:08
  • It looks like the formula may not have been entered in the name manager correctly. With Cell F9 selected, Bring up the name manager and edit the flag name. It should show =AND(Sheet1!$E9>10,Sheet1!$F9<11) assuming this is on sheet1. If not, correct it – bvaughn Dec 15 '16 at 21:51

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.