Conditional formatting color gradient with hard stops

Question

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.

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 up to 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.

Update: Although this is really ugly, I decided to try to figure out which cells are greater than 0 (or actually a midpoint value, ~1.33 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):

<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">
  <x:cfRule type="colorScale" priority="1">
    <x:colorScale>
      <x:cfvo type="num" val="1.13330279612636" />
      <x:cfvo type="num" val="1.91050388235334" />
      <x:color rgb="d6F4d6" />
      <x:color rgb="148621" />
    </x:colorScale>
  </x:cfRule>
</x:conditionalFormatting>

And like this (negative red scale):

<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">
  <x:cfRule type="colorScale" priority="1">
    <x:colorScale>
      <x:cfvo type="num" val="0.356101709899376" />
      <x:cfvo type="num" val="1.13330279612636" />
      <x:color rgb="985354" />
      <x:color rgb="f4dddd" />
    </x:colorScale>
  </x:cfRule>
</x:conditionalFormatting>

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 1.33 that should (and did) have the green gradient rules applied but are now referenced by the red gradient (and so end up pale red).

I tried with both relative and absolute cell references (i.e. minus the $), but that doesn't seem to work either.

Answer 1

I haven't been able to find a way to make this work using default Excel conditional formatting. It is possible to create your own conditional formatting algorithm in VBA that will enable this functionality, however:

Sub UpdateConditionalFormatting(rng As Range)
    Dim cell As Range
    Dim colorValue As Integer
    Dim min, max As Integer

    min = WorksheetFunction.min(rng)
    max = WorksheetFunction.max(rng)

    For Each cell In rng.Cells
        If (cell.Value > 0) Then
            colorValue = (cell.Value / max) * 255
            cell.Interior.Color = RGB(255 - colorValue, 255, 255 - colorValue)
        ElseIf (cell.Value < 0) Then
            colorValue = (cell.Value / min) * 255
            cell.Interior.Color = RGB(255, 255 - colorValue, 255 - colorValue)
        End If

        Next cell
    End
End Sub

The code above will generate the following color scheme, and can be easily modified to fit whatever palette you have in mind:

You can use this code in a macro, or drop it into a Worksheet_Change event and have it updated automatically (note that when installed in the Worksheet_Change event handler you will lose undo functionality):

Sub Worksheet_Change(ByVal Target As Range)
    UpdateConditionalFormatting Range("A1:A21")
End Sub