title: Conditional formatting color gradient with hard stops
body: <p>I have a column of data in an Excel sheet which has positive and negative values. What I want to be able to do is apply conditional formatting (a color gradient) from say dark green to light green for positive values and light red to dark red for negative values.</p>
  
  <p>However, I don't seem to be able to do that. If I apply a conditional format from, say, the largest value to zero, with zero as light green, then all the negative values will end up being light green too. Is there a way to make a conditional format apply only <em>up to</em> a certain value and not beyond? I can similarly make a conditional format for the negative values, but again it will color positive values light red. If I have both in the same sheet, then whichever has the highest priority wins.</p>
  
  <p><strong>Update</strong>: Although this is really ugly, I decided to try to figure out which cells are greater than 0 (or actually a midpoint value, ~<code>1.33</code> in this case) and which are lower and set the cell references explicitly to those cells. So I tried defined conditional formatting like this (positive green scale):</p>
  
  <pre><code>&lt;x:conditionalFormatting sqref="$E$5 $E$6 $E$10 $E$13 $E$15 $E$17 $E$18 $E$19 $E$22 $E$24 $E$25..." xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"&gt;
    &lt;x:cfRule type="colorScale" priority="1"&gt;
      &lt;x:colorScale&gt;
        &lt;x:cfvo type="num" val="1.13330279612636" /&gt;
        &lt;x:cfvo type="num" val="1.91050388235334" /&gt;
        &lt;x:color rgb="d6F4d6" /&gt;
        &lt;x:color rgb="148621" /&gt;
      &lt;/x:colorScale&gt;
    &lt;/x:cfRule&gt;
  &lt;/x:conditionalFormatting&gt;
  </code></pre>
  
  <p>And like this (negative red scale):</p>
  
  <pre><code>&lt;x:conditionalFormatting sqref="$E$4 $E$7 $E$8 $E$9 $E$11 $E$12 $E$14 $E$16 $E$20 $E$21 $E$23 $E$26 $E$28 $E$29 $E$30..." xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"&gt;
    &lt;x:cfRule type="colorScale" priority="1"&gt;
      &lt;x:colorScale&gt;
        &lt;x:cfvo type="num" val="0.356101709899376" /&gt;
        &lt;x:cfvo type="num" val="1.13330279612636" /&gt;
        &lt;x:color rgb="985354" /&gt;
        &lt;x:color rgb="f4dddd" /&gt;
      &lt;/x:colorScale&gt;
    &lt;/x:cfRule&gt;
  &lt;/x:conditionalFormatting&gt;
  </code></pre>
  
  <p>And this works great! Right up until the point you try to sort (I have an auto filter on this sheet) and it screws up the cell assignments. So now I have so values greater than <code>1.33</code> that should (and did) have the green gradient rules applied but are now referenced by the red gradient (and so end up pale red).</p>
  
  <p>I tried with both relative and absolute cell references (i.e. minus the <code>$</code>), but that doesn't seem to work either.</p>