vba 基于颜色的 Excel 条件格式数据条
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30633378/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel Conditional Formatting Data Bars based on Color
提问by TheBlake
I cannot find a way to change the color of an Excel data bar based on value. Current formatting options only permit different colors based on positive/negative values. I'm currently using Excel 2010.
我找不到根据值更改 Excel 数据栏颜色的方法。当前的格式选项仅允许基于正/负值的不同颜色。我目前正在使用 Excel 2010。
I would like to have the color of a data bar show up as 'red' if the value if between 0-0.3, 'yellow' if the value is between 0.3-0.6, and 'green' if the value if between >0.6.
如果值在 0-0.3 之间，我希望数据条的颜色显示为“红色”，如果值在 0.3-0.6 之间，则显示为“黄色”，如果值在 >0.6 之间，则显示为“绿色”。
Would really appreciate any info people could share.
非常感谢人们可以分享的任何信息。
Thanks,
谢谢，
TB
结核病
回答by teylyn
Data bars only support one color per set. The idea is that the length of the data bar gives you an indication of high, medium or low.
数据条每组仅支持一种颜色。这个想法是数据条的长度为您提供高、中或低的指示。
Conditional colors can be achieved with color scales.
可以使用色标实现条件颜色。
What you describe sounds like a combination of the two, but that does not exist in Excel and I don't see an easy way to hack it.
你所描述的听起来像是两者的结合，但这在 Excel 中不存在，我看不出有什么简单的方法可以破解它。
You could use a kind of in-cell "chart" that was popular before sparklines came along. Use a formula to repeat a character (in the screenshot it's the character gformatted with Marlett font), and then use conditional formatting to change the font color.
您可以使用一种在迷你图出现之前流行的单元格内“图表”。使用公式重复一个字符（在屏幕截图中是g使用 Marlett 字体格式化的字符），然后使用条件格式更改字体颜色。
For a nicer "bar" feel, use unicode character 2588 with a regular font.
要获得更好的“条形”感觉，请使用带有常规字体的 unicode 字符 2588。
Edit: Not every Unicode character is represented in every font. In this case the the unicode 2588 shows fine with Arial font but not with Excel's default Calibri. Select your fonts accordingly. The Insert > Symbol dialog will help find suitable characters.
编辑：并非每个 Unicode 字符都以每种字体表示。在这种情况下，unicode 2588 使用 Arial 字体显示正常，但不适用于 Excel 的默认 Calibri。相应地选择您的字体。插入 > 符号对话框将帮助找到合适的字符。
回答by Tohid
This article explains a trick that does the job:
这篇文章解释了一个可以完成这项工作的技巧：
http://www.excel-user.com/2012/05/conditional-formatting-bar-chart.html
http://www.excel-user.com/2012/05/conditional-formatting-bar-chart.html
回答by clarencebuttowski
I setup conditional formatting in the cell adjacent to the data bar that changes color based on the value in the target cell (green, yellow, red, orange). I then loop through the VBA below to update the data bar color based on the conditional formatting in the adjacent cell.
我在与数据栏相邻的单元格中设置条件格式，根据目标单元格中的值（绿色、黄色、红色、橙色）更改颜色。然后我循环遍历下面的 VBA 以根据相邻单元格中的条件格式更新数据栏颜色。
Dim intCount As Integer
Dim db As DataBar
On Error Resume Next
For intCount = 9 To 43 'rows with data bars to be updated
Worksheets("Worksheet Name").Cells(intCount, 10).FormatConditions(1).BarColor.Color = Worksheets("Worksheet Name").Cells(intCount, 11).DisplayFormat.Interior.Color
Next intCount
回答by JR Massel
Instead of creating conditional formatting for a range of cells, I conditionally formatted each cell individually using VBA based on the two subs below. The result is shown in the link below the code. Hope this helps.
我没有为一系列单元格创建条件格式，而是根据以下两个子项使用 VBA 分别有条件地格式化每个单元格。结果显示在代码下方的链接中。希望这可以帮助。
' The purpose of this sub is to add a data bar to an individual cell
' The value in the cell is expected to be decimal numbers between -1 and 1
' If the value is greater than or equal to -0.1 and less than or equal to 0.1, then display green bars
' If the value is less than -0.1 and greater than -.2, OR greater than 0.1 and less than 0.2 then yellow bars
' All other scenarios display red bars
Sub Add_Data_Bar(rngCell As Range, dblValue As Double)
' Clears existing conditional formatting from the cell
' Adds a new data bar to the cell
With rngCell.FormatConditions
.Delete
.AddDatabar
End With
' Creates a databar object for the databar that has been added to the cell
Dim dbar As Databar
Set dbar = rngCell.FormatConditions(rngCell.FormatConditions.Count)
' Sets the databar fill type to display as gradient
dbar.BarFillType = xlDataBarFillGradient
' Sets the databar border style
dbar.BarBorder.Type = xlDataBarBorderSolid
' Sets the databar axis position
dbar.AxisPosition = xlDataBarAxisMidpoint
' Sets the minimum limit of the data bar to -1
With dbar.MinPoint
.Modify newtype:=xlConditionValueNumber, newvalue:=-1
End With
' Sets the maximum limit of the data bar to +1
With dbar.MaxPoint
.Modify newtype:=xlConditionValueNumber, newvalue:=1
End With
' Sets the color based on what value has been passed to the sub
' Green
If dblValue <= 0.1 And dblValue >= -0.1 Then
With dbar
.BarColor.Color = RGB(99, 195, 132) ' Green
.BarBorder.Color.Color = RGB(99, 195, 132)
End With
' Yellow
ElseIf (dblValue > 0.1 And dblValue <= 0.2) Or (dblValue < -0.1 And dblValue >= -0.2) Then
With dbar
.BarColor.Color = RGB(255, 182, 40) ' Yellow
.BarBorder.Color.Color = RGB(255, 182, 40)
End With
' Red
Else
With dbar
.BarColor.Color = RGB(255, 0, 0) ' Red
.BarBorder.Color.Color = RGB(255, 0, 0)
End With
End If
End Sub
' Applies the databar formatting to each cell in a range
‘ Call this on the Worksheet_Change event so that the formatting updates when data is refreshed
Sub Loop_Through_Range()
' Range to be looped through
Dim rng As Range
Set rng = Sheet1.Range("A2:A22")
' Range for For Loop
Dim cell As Range
' Loops through each cell in your range
For Each cell In rng.Cells
Call Add_Data_Bar(cell, cell.Value)
Next
End Sub
回答by keong kenshih
In your case, highlight the cell will be more suitable as we can not form data bar with multiple colors
Conditional Formationg >Manage Rules...>New Rule
Under Select a Rule Type, choose "Use a formula to determince which cells to format" and set your rules there
在您的情况下，突出显示单元格将更合适，因为我们无法形成具有多种颜色的数据栏
条件格式g>管理规则...>新规则
在选择规则类型下，选择“使用公式来确定要格式化的单元格”并在那里设定你的规则
