使用 Excel VBA 比较列并突出显示匹配数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18464708/
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
Comparing Columns and Highlight the Matching Data Using Excel VBA
提问by Alcapone
I Need to compare Column Bin Sheet 2with Column Cin Sheet 3and highlight the cells with matching data in Column B (Sheet 2).
我需要比较Column B的Sheet 2同Column C在Sheet 3，并强调与匹配数据的单元格Column B (Sheet 2)。
Have used the following formula in Conditional Formatting
在条件格式中使用了以下公式
which works =NOT(ISNA(VLOOKUP(Sheet3!C,Sheet2!B,1,FALSE)))with Format type being a
它适用=NOT(ISNA(VLOOKUP(Sheet3!C,Sheet2!B,1,FALSE)))于格式类型为
particular color say Yellow Interior.colorindex = 6
特定颜色说黄色 Interior.colorindex = 6
How to implement the same using code in VBA?
如何在 VBA 中使用代码实现相同的功能？
采纳答案by Alcapone
you can do it like this
你可以这样做
Sub CompareAndHighlight()
Dim rng1 As Range, rng2 As Range, i As Long, j As Long
For i = 1 To Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row
Set rng1 = Sheets("Sheet2").Range("B" & i)
For j = 1 To Sheets("Sheet3").Range("C" & Rows.Count).End(xlUp).Row
Set rng2 = Sheets("Sheet3").Range("C" & j)
If StrComp(Trim(rng1.Text), Trim(rng2.Text), vbTextCompare) = 0 Then
rng1.Interior.Color = RGB(255, 255, 0)
End If
Set rng2 = Nothing
Next j
Set rng1 = Nothing
Next i
End Sub
the code checks all cells in Sheet2 column B against each cell from Sheet3 column C and if they match it highlights cells on Sheet2 in Column B in yellow
该代码根据 Sheet3 C 列中的每个单元格检查 Sheet2 B 列中的所有单元格，如果它们匹配，则以黄色突出显示 B 列中 Sheet2 上的单元格
回答by Joe
I would do it this way:
我会这样做：
Dim c As Range
For Each c In Range("sheet2!b:b")
If c.Value <> "" And Sheets("Sheet3").Cells(c.Row, 3).Value = c.Value Then
c.Interior.Color = vbYellow
End If
Next
