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

Answer 2

I thought this was going to be relatively easy but it took more thought and isn't an elegant solution.

Assuming you have vba background I'll give you the method I'd use - if you need help programming it leave a comment and I'll provide support.

Assumption: Range is sorted Min-Max or Max-Min 'This wont work otherwise

Write a sheet level macro that updates on calculation or selection -- whenever you want the conditional formatting to be updated

in this macro you'll identify the upper and lower bounds of your data range and the location of the midpoint

so in the picture above that'd be

LB = A1
UP = A21
MP = A11

Then you'll simply apply two gradients w/ an if statement you said the midpoint wont ever be exact so the if statement will determine if the midpoint belongs to the upper or lower range

then just:

Range(LB:MP).Select .......apply traditional conditional format 1 (CF1)
Range(MP+1:UP).Select .......apply traditional conditional format 2 (CF2)
or
Range(LB:MP-1).Select .......apply traditional conditional format 1
Range(MP:UP).Select .......apply traditional conditional format 2

I wouldn't use white as the MP color, but in CF1 if it is the red range I'd use light red to dark red, and CF2 light green to dark green

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

I just read your sort dilemma.

Another solution, I've used in the past, and again if you need coding support I can try to look for my old code

I used a simple regression on the RGB (even easier if you're only going G or R) to actually assign a color number to each value

MP = (0,1,0)
UP = (0,255,0)
MP-1 = (1,0,0)
LB = (255,0,0)

again with the same sheet macro and MP if logic as above

and then I just iterated through the cells and applied the color

if cellVal < MP then CellVal*Mr+Br 'r for red, M & B for slope & intercept
if cellVal > MP then CellVal*Mg+Bg 'g for green, M & B for slope & intercept

If that's not clear let me know, and again if you need help w/ the code I can provide it.

-E

Edit 2:

You could, and I would recommend, instead of iterating through the entire range, only iterate through the visible range - this will speed it up even more, and you could add the trigger to the sort/filter command of your table/dataset - it'd also give you the freedom to choose if you want the color spectrum to be based on all of your data or just the visible data - w/ the latter you'd be able to do some 'cool' things like look just above the 95th percentile and still see color differentiations where as w/ the former they'd most likely all be G 250-255 and harder to discern

Answer 3

After your comment to my previous answer, I don't think this is possible using a colorScale as you'd either need two scales or one with four colors (neither of which is allowed). You could create your own though by using conditional formats with formulae.

Using this approach you can make it work without the need for VBA and any sorting or editing of the sheet will still work.

I've knocked up a (very) rough example together that shows how this could work. It's a bit rough in as much as it will create a new conditional format for every value; it would be neater to create one per range that you're interested in (maybe using percentiles) but it's a starting point.

The bulk of the work is done in the following two methods. I've added some summary comments to them, if they need more explanation just let me know.

/// <summary>
/// Adds a conditional format to the sheet based on the value passed in
/// </summary>
/// <param name="value">The value going into the cell</param>
/// <param name="minValue">The minimum value in the whole range of values going into the sheet</param>
/// <param name="maxValue">The maximum value in the whole range of values going into the sheet</param>
/// <param name="ignoreRangeLowValue">The lowest value in the mid-point. A value greater than or equal to this and less than or equal to the ignoreRangeHighValue will be unstyled</param>
/// <param name="ignoreRangeHighValue">The highest value in the mid-point. A value greater than or equal to the ignoreRangeLowValue and less than or equal to this value will be unstyled</param>
/// <param name="lowValuesMinColor">The colour of the lowest value below the mid-point</param>
/// <param name="lowValuesMaxColor">The colour of the highest value below the mid-point</param>
/// <param name="highValuesMinColor">The colour of the lowest value above the mid-point</param>
/// <param name="highValuesMaxColor">The colour of the highest value above the mid-point</param>
/// <param name="differentialFormats">A DifferentialFormats object to add the formats to</param>
/// <param name="conditionalFormatting">A ConditionalFormatting object to add the conditional formats to</param>
private static void AddConditionalStyle(decimal value,
                                decimal minValue,
                                decimal maxValue,
                                decimal ignoreRangeLowValue,
                                decimal ignoreRangeHighValue,
                                System.Drawing.Color lowValuesMinColor,
                                System.Drawing.Color lowValuesMaxColor,
                                System.Drawing.Color highValuesMinColor,
                                System.Drawing.Color highValuesMaxColor,
                                DifferentialFormats differentialFormats, 
                                ConditionalFormatting conditionalFormatting)
{
    System.Drawing.Color fillColor;

    if (value >= ignoreRangeLowValue && value <= ignoreRangeHighValue)
        return;

    if (value > ignoreRangeHighValue)
    {
        fillColor = GetColour(value, ignoreRangeHighValue, maxValue, highValuesMinColor, highValuesMaxColor);
    }
    else
    {
        fillColor = GetColour(value, minValue, ignoreRangeLowValue, lowValuesMinColor, lowValuesMaxColor);
    }

    DifferentialFormat differentialFormat = new DifferentialFormat();
    Fill fill = new Fill();
    PatternFill patternFill = new PatternFill();
    BackgroundColor backgroundColor = new BackgroundColor() { Rgb = fillColor.Name };
    patternFill.Append(backgroundColor);
    fill.Append(patternFill);
    differentialFormat.Append(fill);
    differentialFormats.Append(differentialFormat);

    ConditionalFormattingOperatorValues op = ConditionalFormattingOperatorValues.Between;
    Formula formula1 = null;
    Formula formula2 = null;

    if (value > maxValue)
    {
        op = ConditionalFormattingOperatorValues.GreaterThanOrEqual;
        formula1 = new Formula();
        formula1.Text = value.ToString();
    }
    else if (value < minValue)
    {
        op = ConditionalFormattingOperatorValues.LessThanOrEqual;
        formula1 = new Formula();
        formula1.Text = value.ToString();
    }
    else
    {
        formula1 = new Formula();
        formula1.Text = (value - 0.05M).ToString();
        formula2 = new Formula();
        formula2.Text = (value + 0.05M).ToString();
    }

    ConditionalFormattingRule conditionalFormattingRule = new ConditionalFormattingRule()
    {
        Type = ConditionalFormatValues.CellIs,
        FormatId = (UInt32Value)formatId++,
        Priority = 1,
        Operator = op
    };

    if (formula1 != null)
        conditionalFormattingRule.Append(formula1);

    if (formula2 != null)
        conditionalFormattingRule.Append(formula2);

    conditionalFormatting.Append(conditionalFormattingRule);
}

/// <summary>
/// Returns a Color based on a linear gradient
/// </summary>
/// <param name="value">The value being output in the cell</param>
/// <param name="minValue">The minimum value in the whole range of values going into the sheet</param>
/// <param name="maxValue">The maximum value in the whole range of values going into the sheet</param>
/// <param name="minColor">The color of the low end of the scale</param>
/// <param name="maxColor">The color of the high end of the scale</param>
/// <returns></returns>
private static System.Drawing.Color GetColour(decimal value,
                                        decimal minValue,
                                        decimal maxValue,
                                        System.Drawing.Color minColor,
                                        System.Drawing.Color maxColor)
{
    System.Drawing.Color val;

    if (value < minValue)
        val = minColor;
    else if (value > maxValue)
        val = maxColor;
    else
    {
        decimal scaleValue = (value - minValue) / (maxValue - minValue);

        int r = (int)(minColor.R + ((maxColor.R - minColor.R) * scaleValue));
        int g = (int)(minColor.G + ((maxColor.G - minColor.G) * scaleValue));
        int b = (int)(minColor.B + ((maxColor.B - minColor.B) * scaleValue));

        val = System.Drawing.Color.FromArgb(r, g, b);
    }

    return val;
}

As an example usage I've created this:

static uint formatId = 0U;
public static void CreateSpreadsheetWorkbook(string filepath)
{
    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.
        Create(filepath, SpreadsheetDocumentType.Workbook);

    // Add a WorkbookPart to the document.
    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
    workbookpart.Workbook = new Workbook();

    // Add a WorksheetPart to the WorkbookPart.
    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();

    SheetData sheetData = new SheetData();
    worksheetPart.Worksheet = new Worksheet(sheetData);

    // Add Sheets to the Workbook.
    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.
        AppendChild<Sheets>(new Sheets());

    // Append a new worksheet and associate it with the workbook.
    Sheet sheet = new Sheet()
    {
        Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart),
        SheetId = 1,
        Name = "FormattedSheet"
    };
    sheets.Append(sheet);

    WorkbookStylesPart stylesPart = workbookpart.AddNewPart<WorkbookStylesPart>();
    stylesPart.Stylesheet = new Stylesheet();

    Fills fills = new Fills() { Count = (UInt32Value)20U }; //this count is slightly out; we should calculate it really

    //this could probably be more efficient - we don't really need one for each value; we could put them in percentiles for example
    DifferentialFormats differentialFormats = new DifferentialFormats() { Count = (UInt32Value)20U };

    ConditionalFormatting conditionalFormatting = new ConditionalFormatting() { SequenceOfReferences = new ListValue<StringValue>() { InnerText = "A1:A21" } };

    for (decimal i = 1; i > -1.1M; i -= 0.1M)
    {
        AddConditionalStyle(i, -0.8M, 0.8M, 0M, 0M,
                            System.Drawing.Color.FromArgb(152, 83, 84),
                            System.Drawing.Color.FromArgb(244, 221, 221),
                            System.Drawing.Color.FromArgb(214, 244, 214),
                            System.Drawing.Color.FromArgb(20, 134, 33),
                            differentialFormats,
                            conditionalFormatting);
    }

    worksheetPart.Worksheet.Append(conditionalFormatting);
    stylesPart.Stylesheet.Append(differentialFormats);

    uint rowId = 1U;
    for (decimal i = 1; i > -1.1M; i -= 0.1M)
    {
        Cell cell = new Cell();
        cell.DataType = CellValues.Number;
        cell.CellValue = new CellValue(i.ToString());

        Row row = new Row() { RowIndex = rowId++ };
        row.Append(cell);
        sheetData.Append(row);
    }

    workbookpart.Workbook.Save();

    spreadsheetDocument.Close();
}

Which creates a spreadsheet with that looks like this:

Answer 4

You could perhaps use the num type on the cfvo to define the midpoint as zero with a colour of white. Then set the min to red and the max to green.

Something like this for example

<conditionalFormatting sqref="A1:A21">
   <cfRule type="colorScale" priority="1">
      <colorScale>
         <cfvo type="min" />
         <cfvo type="num" val="0" />
         <cfvo type="max" />
         <color rgb="ff0000" />
         <color rgb="ffffff" />
         <color rgb="00ff00" />
      </colorScale>
   </cfRule>
</conditionalFormatting>

gives a result that looks like this:

Answer 5

Based on your further comments, I see that your particular concern with using the 3 colour gradient is a lack of distinction around the 'transition' point. I recommend based on that note that you actually use multiple sets of conditional formatting rules, on overlapping sections with specific priority, as follows:

Assume we're looking at column A, which will hold numbers from -100 to 100. Assume that you want anything -100 or worse to be bright red, with a gradual dissapation down to light at near 0. Then at, say, +.5 to -.5, you want colourless white. Then near 0 it should go light green, up to bright green at +100.

First, set the rule regarding the '0' section. Something like:

=ROUND(A1,0)=0

Make this rule apply in priority, and set it to make the cell white. Note that you could also use this to 'white out' distant cases. Something like:

=OR(ROUND(A1,0)=0,ROUND(A1,0)>100,ROUND(A1,0)<-100)

This rule would make cells at 0 white, and outside your desired -100->100 range white.

THEN apply a second rule, which includes your gradients. This would set 3 colours, with white at 0 (even though your hardcoded 'rounded to 0' rule would apply overtop, eliminating the gradual colour around the number 0), red at -100, and green at 100.

On this basis, anything outside the range -100->100 range would be white, anything which rounds to 0 would be white, and any other number in the range would move evenly from bright red, to white, to bright green.

Answer 6

I am just a novice of vba, and I thought this was an interesting question. I am unsure as to how the rules apply to posting "brainstorming" solutions, so if I am stepping on some toes, let me know and I will remove my answer and learn to avoid doing it in the future. Humble introduction being said:

Can you modify the conditional formatting via vba? I would investigate whether it is possible to do this: Reading on the formatting properties and objects in vba conditional formatting it seems possible to use the TintAndShade

Rule #1: Color everything larger than desired midpoint monocolor green (via formula)

Rule #2: Opposite side in monocolor red

Rule #3: 3-way gradient -> modified in vba

Modify it - remove the color references but add a line where you set

 .TintAndShade = .Gradient 

inside an IF

IF .Gradient < 0.3 Then .TintAndShade = 0.3 Else .TintAndShade = .Gradient End if

(0.3 is my suggestion. At least on my screen green @ 0.3 tint is visibly distinct from red @ 0.3)

Answer 7

Does Graded Color Scale under Conditional Formatting not work? To set zero as the midpoint and remain white, under "Edit the Rule Description", change the Midpoint type to number and set number to zero, or to a formula such as AVERAGE or whatever is needed