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.

You mention in a comment below that a 3-colour palette is not exactly what you want - my recommendation would be that you define the limits of your 3 colours dynamically in other cells, and then refer to those cells for the limits. For example, I've done this where I wanted 0 to be white, with negative numbers scaling up to dark red, but with a capped limit [ie: -5000 was the deepest red possible, and anything lower looked the same]. I set that cap in another cell, and used it as the limit for that colour of the 3 scale approach. — Grade 'Eh' Bacon
@Grade'Eh'Bacon: The problem is that a number slightly above 0 (or whatever the mid point is) should be light green, not white, light green, say #d6F4d6, and a number slight below 0 should be light red, not white, say #f4dddd. You can't make that sharp transition from green to red with a three color gradient. It'll blend to white at 0 and that's not the effect we are looking for. — Matt Burland
Ah, I understand - I had thought at first you were wanting the opposite - a hard cap on the end colours (with higher/lower amounts keeping the same colour), rather than a discrete section where the rule doesn't apply. See my answer for a relatively painless method, using multiple conditional formatting rules with specific priorities. — Grade 'Eh' Bacon
Would you please clarify the requirements: 1) Midpoint to be light green? 2) Everything above that midpoint to apply a gradient color with the highest value as dark green? 3) Values below midpoint apply gradient color with closest value to zero as light red and lowest value as dark red? 4) All the above applies even if the midpoint is higher than zero or lower than zero? Or 5) You want to force zero as the visual midpoint? (i.e. always light green and move from there even if the actual midpoint is higher or lower than zero)? — EEM
Possible workaround is to create ~10 formula based conditional formatting for both green and red colors. Simple formula like =IF(A1>0,A1/MAX($A$1:$A$24),-A1/MIN($A$1:$A$24))>0.9 for each step should work. Far from perfect, but could do the job. — BrakNicku

Brett Wolfington Brett Wolfington · Accepted Answer · 2015-11-25T21:13:08

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

Conditional formatting color gradient with hard stops

7 Answers

--------------------------------------Edit--------------------------------------