Excel VBA，比较多列值，颜色单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20019815/
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 VBA, compare multiple column values, color cells
提问by user2999536
I'm new to VBA in Excel and I'm having some trouble. I have 3 columns A, C, and F. I want to highlight only the cells in those columns if they match either of 2 conditions. Highlight any cells with duplicate values in column A, and then only highlight cells in column C and F if column C has a value of 99.99 and greater and Column F has anything but "Test" in the cell.
我是 Excel 中的 VBA 新手，遇到了一些麻烦。我有 3 列 A、C 和 F。如果这些列中的单元格与 2 个条件中的任何一个匹配，我只想突出显示这些列中的单元格。突出显示 A 列中具有重复值的任何单元格，然后仅突出显示 C 列和 F 列中的单元格，如果 C 列的值大于或等于 99.99，并且 F 列的单元格中没有“测试”以外的任何内容。
Sub Highlight()
Dim index As Integer
For index = 1 To 4
'Checks if any cells in Column C has value greater than 99.99 when Column F isn't "Test" or checks if multiple values exist in Column A (which I don't know)
If Range("C1") And Cell.Value > "99.99" And Range("F1") And Cell.Text <> "Current" Then
'Highlighs both cell values Yellow (this is where I run into trouble)
Cell.Interior.ColorIndex = vbYellow
End If
Next index
End Sub
采纳答案by Sam
You were just referring to your range wrong. Cellwasn't set. I couldn't see where you were referring to column A though.
你只是指错了你的范围。Cell没有设置。不过，我看不出你指的是 A 列。
Also, if you're using the built in color constants you should use .Colornot ColorIndex.
此外，如果您使用内置颜色常量，则不应使用.ColorColorIndex。
Sub Highlight()
Dim index As Integer
Dim ws As Worksheet
'set the sheet to use
Set ws = Sheet1
For index = 1 To 4
'Checks if any cells in Column C has value greater than 99.99 when Column F isn't "Test" or checks if multiple values exist in Column A (which I don't know)
If ws.Range("C" & index).Value > "99.99" And ws.Range("F" & index).Text <> "Current" Then
'Highlighs both cell values Yellow (this is where I run into trouble)
ws.Range("C" & index).Interior.Color = vbYellow
ws.Range("F" & index).Interior.Color = vbYellow
End If
Next index
End Sub
On a side note. You may be better off looking at using Conditional formatting to achieve what you want rather than VBA.
在旁注。您最好考虑使用条件格式而不是 VBA 来实现您想要的。
There is a lot of tutorials about on the internet:
网上有很多教程：
http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/
http://chandoo.org/wp/2009/03/13/excel-conditional-formatting-basics/
http://spreadsheets.about.com/od/advancedexcel/tp/090822-excel-conditional-formatting-hub.htm
http://spreadsheets.about.com/od/advancedexcel/tp/090822-excel-conditional-formatting-hub.htm
