条件格式化颜色渐变与硬停止 [英] Conditional formatting color gradient with hard stops

问题描述

我在Excel表中有一列数据，其中有正值和负值。我想要做的是应用条件格式（颜色渐变），从深绿色到浅绿色为正值，浅红色为深红色为负值。

<不过，我似乎无法做到这一点。如果我将条件格式从最大值应用到零，零为浅绿色，则所有负值都将变为浅绿色。有没有办法使条件格式仅适用于一定值，而不是超过？我可以类似地为负值创建一个条件格式，但是它也会将正值颜色淡红色。如果我在同一张表中，则以优先级最高者为准。

更新：虽然这真的很丑，我决定尝试找出哪些单元格大于0（或者实际上是一个中点值，在这种情况下为 1.33 ），它们较低，并将单元格引用显式设置为这些单元格。所以我尝试这样定义的条件格式（正绿色标度）：

 < 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 /主> 
< x：cfRule type =colorScalepriority =1> 
< x：colorScale> 
< x：cfvo type =numval =1.13330279612636/> 
< x：cfvo type =numval =1.91050388235334/> 
< x：color rgb =d6F4d6/> 
< x：color rgb =148621/> 
< / x：colorScale> 
< / x：cfRule> 
< / x：conditionalFormatting> 
  

像这样（负红色比例）：

 < 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 =colorScalepriority =1> 
< x：colorScale> 
< x：cfvo type =numval =0.356101709899376/> 
< x：cfvo type =numval =1.13330279612636/> 
< x：color rgb =985354/> 
< x：color rgb =f4dddd/> 
< / x：colorScale> 
< / x：cfRule> 
< / x：conditionalFormatting> 
  

这个功能非常棒！直到你尝试排序的点（我在这张表上有一个自动过滤器），并拧紧单元格分配。所以现在我有这样的值大于 1.33 ，应该（并且已经）应用了绿色渐变规则，但现在被红色渐变引用（因此最终变成淡红色）

我尝试使用相对和绝对单元格引用（即减去 $ ），但不会似乎也可以工作。

解决方案

我无法找到一种使用默认的Excel条件格式化方法。可以在VBA中创建自己的条件格式化算法，然后启用此功能：

  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）
 
对于每个单元格在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）然后
 colorValue =（cell.Value / min）* 255 
 cell.Interior.Color = RGB（255，255  -  colorValue，255  -  colorValue）
如果
 
下一个单元格
结束
 End Sub 
  

上面的代码将生成以下颜色方案，并且可以轻松修改以适应任何调色板你记住：

您可以使用代码，或将其放入Worksheet_Change事件并自动更新（请注意，当安装在Worksheet_Change事件处理程序中时，将失去撤消功能）：

<$ p $
更新条件格式范围（A1：A21）
End Sub

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.

解决方案

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

这篇关于条件格式化颜色渐变与硬停止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！