vba Excel 电子表格中的多种单元格背景颜色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/269267/
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
Multiple Cell Background Colors in Excel Spreadsheets
提问by Michael
Excel has a Conditional Formatting... option under the Format menu that allows you to change the style/color/font/whatever of a cell depending upon its value. But it only allows three conditions.
Excel 在“格式”菜单下有一个条件格式...选项，可让您根据单元格的值更改样式/颜色/字体/任何内容。但它只允许三个条件。
How do I get Excel to display say, six different background cell colors depending upon the value of the cell? (IE Make the cell red if the value is "Red", and blue if "Blue" etc.)
如何让 Excel 根据单元格的值显示六种不同的背景单元格颜色？（IE 如果值为“红色”，则将单元格设为红色，如果值为“蓝色”，则设为蓝色等）
回答by Galwegian
You will need to write something in VBA.
你需要用 VBA 写一些东西。
See example here: Get Around Excels 3 Criteria Limit in Conditional Formatting:
请参见此处的示例：绕过 Excels 3 条件格式中的标准限制：
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If
End Sub
回答by Mike Woodhouse
Excel 2007 allows more than three conditions. Quoting from this Microsoft page:
Excel 2007 允许超过三个条件。引用此 Microsoft 页面：
EDIT: Ah, there's a "feature" in the linking code: parentheses in a link cited in parentheses aren't being handled correctly. That link is: http://msdn.microsoft.com/en-us/library/bb286672(office.11).aspx
编辑：啊，链接代码中有一个“功能”：括号中引用的链接中的括号没有得到正确处理。该链接是：http: //msdn.microsoft.com/en-us/library/bb286672(office.11).aspx
Other benefits of the changes to conditional formatting in Excel 2007 are the ability to specify more than three conditions, to reorder conditions, and to have more than one condition resolve to True.
Excel 2007 中条件格式更改的其他好处是能够指定三个以上的条件、重新排序条件以及将多个条件解析为 True。
Otherwise. you're stuck with messy alternatives as described, I'm afraid.
除此以外。恐怕你被描述的凌乱替代品所困扰。
回答by Russ Cam
put this in a module in your VBA project. You can then highlight a range in a sheet and run the sub from the Tools > Macro > Macros menu item to color each cell in the selected range.
把它放在你的 VBA 项目的一个模块中。然后，您可以突出显示工作表中的一个范围，并从“工具”>“宏”>“宏”菜单项运行子项，为所选范围内的每个单元格着色。
Public Sub ColorCells()
Dim cell, rng As Range
Dim color As Integer
Dim sheet As Worksheet
Application.ScreenUpdating = False
Application.StatusBar = "Coloring Cells"
Set rng = Application.Selection
Set sheet = Application.ActiveSheet
For Each cell In rng.cells
Select Case Trim(LCase(cell))
Case "blue"
color = 5
Case "red"
color = 3
Case "yellow"
color = 6
Case "green"
color = 4
Case "purple"
color = 7
Case "orange"
color = 46
Case Else
color = 0
End Select
sheet.Range(cell.Address).Interior.ColorIndex = color
Next cell
Application.ScreenUpdating = True
Application.StatusBar = "Ready"
End Sub
If users are entering new color names into cells then you could put this in the sheet code in the VBA project to color the cells as a user is entering the color names into cells
如果用户在单元格中输入新的颜色名称，那么您可以将其放入 VBA 项目的工作表代码中，以便在用户将颜色名称输入单元格时为单元格着色
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.cells.Count > 1 Then Exit Sub
Dim color As Integer
Select Case Trim(LCase(Target))
Case "blue"
color = 5
Case "red"
color = 3
Case "yellow"
color = 6
Case "green"
color = 4
Case "purple"
color = 7
Case "orange"
color = 46
Case Else
color = 0
End Select
Target.Interior.ColorIndex = color
End Sub
EDIT: Added Trim function around the case statement expression to test, so that accidental leading/trailing spaces in cells are ignored :)
编辑：在 case 语句表达式周围添加 Trim 函数进行测试，以便忽略单元格中意外的前导/尾随空格:)
回答by Russ Cam
You can use VBA macros to do this...
您可以使用 VBA 宏来执行此操作...
here is one vba macro that might be better if need lots of cases http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/
这是一个 vba 宏，如果需要很多情况可能会更好 http://chandoo.org/wp/2008/10/14/more-than-3-conditional-formats-in-excel/
you need to preformat 'n' cells with the way you want to format your entire range. and then use the macro in that url to get the effect.
您需要以您想要格式化整个范围的方式预先格式化“n”个单元格。然后使用该 url 中的宏来获得效果。
