title: Dynamically formatting a cell based on a range
body: <p>I've read a lot of "conditional formatting" answers, and none of them seem to cove THIS aspect of what I'm trying to do....</p>
  
  <p>I have two questions: (Excel 2010 - windows XP)
  1) I have a column that calculates a percentage per row - I also have another cell in that row that has a "base" or "reference" percentage... I want to HIGHLIGHT the cell where the calculated percentage falls OUTSIDE a range based on the "base" percent.</p>
  
  <p>example - base percent is 15.5% (A1) - the calculated row percent is 17% (AA1) -
  I want something like this
  IF AA1 &lt; sum(A1*.9) AND AA1 > sum(A1*1.1) THEN highlight cell A1</p>
  
  <p>the caveat is that all the "conditional formatting" that does something similar - using the NOT BETWEEN - always seems to convert the cell reference to be ABSOLUTE...meaning when I paste that formula into AA2 or AA3 - it keeps the reference as $A$1...</p>
  
  <p>I need it to change per row....</p>
  
  <p>2) Second question - unrelated - is there some way to LOCK that column ("AA" in my example) so people can't paste data into my formula area?????</p>
  
  <p>thx</p>