CODEWITHSUNDEEP
vbaexcel
pexpex223
pexpex223

Reputation: 371

compare 2 cells in excel by using vba

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.

Input & output:

  1. The value of cell A1 is already in the excel.
  2. Manually enter a value in Cell B1.
  3. click on a button_click sub to see whether the value on 2 cells are the same or not.
  4. Show "Yes" or "No" on cell C1

Excel formula:

=IF(A1=B1,"yes","no")

Upvotes: 9

Views: 127265

Answers (5)

Madhushree
Madhushree

Reputation: 11

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

Upvotes: 0

Eswin
Eswin

Reputation: 29

If (Range("A1").Value = Range("B1").Value) Then
    Range("C1").Value = "Yes"
Else
    Range("C1").Value = "No"
End If

Upvotes: 2

Paul Kelly
Paul Kelly

Reputation: 985

You can use the IIF function in VBA. It is similar to the Excel IF

[c1] = IIf([a1] = [b1], "Yes", "No")

Upvotes: 0

Chrismas007
Chrismas007

Reputation: 6105

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.

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.

Upvotes: 0

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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.

Upvotes: 4

Related Questions