vba 使用vba比较excel中的2个单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28071459/
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
compare 2 cells in excel by using vba
提问by pexpex223
I would like to compare 2 cells' value and see whether they are match or not. I know how to do it on excel but I dont' know how to put it vba code.
我想比较 2 个单元格的值，看看它们是否匹配。我知道如何在 excel 上做到这一点，但我不知道如何把它写成 vba 代码。
Input & output:
输入输出：
- The value of cell A1 is already in the excel.
- Manually enter a value in Cell B1.
- click on a button_click sub to see whether the value on 2 cells are the same or not.
- Show "Yes" or "No" on cell C1
- 单元格A1的值已经在excel中了。
- 在单元格 B1 中手动输入一个值。
- 单击 button_click 子以查看 2 个单元格上的值是否相同。
- 在单元格 C1 上显示“是”或“否”
Excel formula:
excel公式：
=IF(A1=B1,"yes","no")
采纳答案by Paul Kelly
You can use the IIF function in VBA. It is similar to the Excel IF
您可以在 VBA 中使用 IIF 函数。它类似于 Excel IF
[c1] = IIf([a1] = [b1], "Yes", "No")
回答by Gary's Student
Give this a try:
试试这个：
Sub CompareCells()
If [a1] = [b1] Then
[c1] = "yes"
Else
[c1] = "no"
End If
End Sub
Assign this code to the button.
将此代码分配给按钮。
回答by Eswin
If (Range("A1").Value = Range("B1").Value) Then
Range("C1").Value = "Yes"
Else
Range("C1").Value = "No"
End If
回答by Chrismas007
Here is an on change Sub (code MUST go in the sheet module). It will only activate if you change a cell in column B.
这是一个 on change Sub（代码必须进入工作表模块）。仅当您更改 B 列中的单元格时它才会激活。
Private Sub Worksheet_Change(ByVal Target As Range)
If Target is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Column <> 2 Then Exit Sub
If Cells(Target.Row, 1).Value = Cells(Target.Row, 2).Value Then
Cells(Target.Row, 3).Value = "Yes"
Else
Cells(Target.Row, 3).Value = "No"
End If
End Sub
For the record, this doesn't use a button, but it accomplishes your goal of calculating if the two cells are equal any time you manually enter data into cells in Col B.
作为记录，这不使用按钮，但它可以实现您在任何时候手动将数据输入到 Col B 中的单元格时计算两个单元格是否相等的目标。
回答by Madhushree
Sub CompareandHighlight()
Dim n As Integer
Dim sh As Worksheets
Dim r As Range
n = Worksheets("Indices").Range("E:E").Cells.SpecialCells(xlCellTypeConstants).Count
Application.ScreenUpdating = False
Dim match As Boolean
Dim valE As Double
Dim valI As Double
Dim i As Long, j As Long
For i = 2 To n
valE = Worksheets("Indices").Range("E" & i).Value
valI = Worksheets("Indices").Range("I" & i).Value
If valE = valI Then
Else:
Worksheets("Indices").Range("E" & i).Font.Color = RGB(255, 0, 0)
End If
Next i
Application.ScreenUpdating = True
End Sub
