Making a pop up window whenever there is a change in cell value

Question

I wrote a simple macro

=IF(C2=H2,1,))

so whenver value of C2 equals value of H2, pop up window saying "HI" appears

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Set rng = Range("H2")
    If Not Intersect(Target, rng) Is Nothing Then
        If rng = "1" Then
            MsgBox "Cell " & _
                   rng.Address & " = hi"
        End If
    End If

    Set rng = Nothing
End Sub

The problem is that I need it to make sound as well as pop-up and more importantly I need it to do it for a 1000 rows(ie. c2,c3,c4 so to c1000). Don't tell me I need to paste and copy thousand time.

score 1 · Answer 1 · answered Jun 17 '13 at 13:59

It is hard to understand what exactly you are trying to accomplish because your question is not very clear. If I understood correctly, when a value is changed in column H you would like to check to see if it matches the corresponding cell in column C. If the values match you want to display a message box and play a sound. This code will do exactly that. If I have misunderstood the goal please clarify and I will do what I can to help.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    On Error Resume Next
    If Target.Column = 8 Then
        If Target.Value = Target.Offset(0, -5).Value Then
            Beep
            MsgBox "Cell " & Target.Address(False, False) & " = Hi"
        End If
    End If
    On Error GoTo 0
End Sub

+ 1 Ripster :) Your code showed me something that I was missing... Incorporating that in my code. (See Edit) — Siddharth Rout, Jun 17 '13 at 14:10

score 1 · Answer 2 · edited May 23 '17 at 11:56

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not Intersect(Target, Range("C2:C1000")) Is Nothing Then
        If Target.Value = Target.Offset(, 5).Value Then
            Beep
            MsgBox "Blah Blah"
        End If
    '~~> Added Code to handle changes in Col H as well. Thanks to ripster
    ElseIf Not Intersect(Target, Range("H2:H1000")) Is Nothing Then
        If Target.Value = Target.Offset(, -5).Value Then
            Beep
            MsgBox "Blah Blah"
        End If
    End If

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

I would always recommend using proper error handling. You might also want to see this?

The above code doesn't handle the situation if there is a multiple paste. Let me know if you want to check for that as well :)

+1 for proper error handling and checking changes in both col C and H — Ripster, Jun 17 '13 at 14:14

Making a pop up window whenever there is a change in cell value

2 Answers2