If two cells are different

[DaleKeel]

I paste in a number in a cell that is linked to cell I4 (so that changes I4 cell's number) Then I paste the same number from a different location in another cell that is linked to cell E4. So that both cells stay the same all the time. Sometimes I do not paste in the cell that shows up in I4 or I do not paste in the number that shows up in E4 and it causes me a lot of problems when both are out of sync for very long. The pasting operation usually only takes less than 30 seconds but I do this often every workday. By having an alert that tells me "Something if Wrong" once I can correct the situation before proceeding on to the next operation. But I only need to hear it played once.

I would like to have a marco that

Any time cell I4 is different than cell E4 for 60 seconds would sound alert located in windows media folder by the name wrong.wav
Play alert only once.

Thanks much.

Dale

 

[mole999]

that does mean everytime you change a value you will have the alert, and I wouls suspect very annoying. An alternate is to have a cell that watches both like =IF(D4<>I4,"Cells do not match","") and if necessay set a CF that watches that cell and colours it if the word match (or other) occurs

 

[Yongle]

@mole999 approach is simple and simper is usually better

Another way would be to use a simple selection event to make the sheet appear different when the values do not tally
as an example, the code below makes the window zoom to 200% until the values are the same

Place in sheet code window

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If [I4] <> [E4] Then ActiveWindow.Zoom = 200 Else ActiveWindow.Zoom = 100
End Sub

 

[mole999]

@Yongle, would this not evaluate quicker

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If [I4] = [E4] Then ActiveWindow.Zoom = 100 Else ActiveWindow.Zoom = 200
End Sub

just a thought

 

[Yongle]

@mole999 , yes that would be better.

 

[DaleKeel]

I am sorry but neither Yongle or mole999 understand my request.

I want the alert to be set for 60 seconds later and to only notify me once. This delay would give me enough time to (do what I needed to do and) paste the number into the E4 linked cell. So it would annoy me only if I made the mistake of not pasting the number (in the first linked cell) or not pasting the number (in the second linked cell) in time to prevent the alert.

I stated that I was pasting the number into a cell "linked" to I4 and and another one linked to E4. Sorry I should have mentioned the linked cells are on different sheets so the "zooming" would not alert me.

I do appreciate your time and thoughts on this issue. Hopefully your suggestions can help another person in need.

If is impossible to delay the alert for a time period I would be very happy for an alert that would just play the wrong.wav in the windows media folder "just once" when cells I4 and E4 did not match. I have other alerts that use the system(?) beep sound so to use this would not really alert me for this problem.

Again I thank both of you.

Dale

 

[DaleKeel]

I saw this on another web page but he says it does not work and I really do not know exactly what is does.

Option Explicit

Private Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim CheckRange As Range
Dim PlaySound As Boolean

Set CheckRange = Range("C:C")
For Each Cell In CheckRange
If Cell.Value = "#N/A" Then
PlaySound = True
End If
Next
If PlaySound Then
Call sndPlaySound32("C:\windows\media\chord.wav", 1)
End If
End Sub

I have cells from E4:I30 that would(could) change throughout the workday. But never more than one pair of matching cells at a time.
I do not understand what the "winmm.dll" is all about. And instead of playing the chord.wav once I would just change this to wrong.wav (that I have place there)
I think this is saying if there is an "N/A" that the alert would go off. That is not what would happen. The cells in the range would always match each other(pairs) except when I was making changes to one of the pairs.
The sheet that I want the alert on is named "ANALYSIS" I did not mention this before.
I hope I am not confusing everything by putting this reply here. But it would be really great to treat all of the pairs in a range instead of by single pairs.

 

[DaleKeel]

This is someone's response to the one that did not work. If this one works it still does not really fix my problem as it seems to be looking for text.

Option Explicit

Private Declare Function sndPlaySound32 Lib "winmm.dll" _
Alias "sndPlaySoundA" (ByVal lpszSoundName _
As String, ByVal uFlags As Long) As Long

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cell As Range
Dim CheckRange As Range
Dim PlaySound As Boolean

Set CheckRange = Range(Cells(1, 3), Cells(ActiveSheet.UsedRange.Rows.Count, 3))
For Each Cell In CheckRange
If Cell.Text = "#N/A" Then
PlaySound = True
Exit For
End If
Next
If PlaySound Then
Call sndPlaySound32("C:\windows\media\chord.wav", 1)
End If
End Sub

 

[Yongle]

I am sorry but neither Yongle or mole999 understand my request

That is quite incorrect
- we both understood but gave you alternative approach which you rejected

Try this
- it is simple and will remind you as you move around or change values in either sheet
(tested with Office 365 on Windows 10)

.
Place this code AT THE TOP of a module( Module 1, Module 2 etc)
- amend "Name of Sheet" to the sheet containing E4 & I4

Option Explicit
Private Declare Function sndPlaySound32 Lib "winmm.dll" _
    Alias "sndPlaySoundA" (ByVal lpszSoundName As String, ByVal uFlags As Long) As Long

Sub PlaySound()
    With Sheets("Name of Sheet")
        If .Range("E4") <> .Range("I4") Then Call sndPlaySound32("C:\windows\media\chord.wav", 1)
    End With
End Sub

.
Copy this code into ThisWorkbook code window (not a module or a sheet code window - it will not work)
- amend the 2 sheet names

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
        Case "Name of Sheet", "Other Sheet"
            Call PlaySound
    End Select
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Select Case Sh.Name
        Case "Name of Sheet", "Other Sheet"
            Call PlaySound
    End Select
End Sub

 

[DaleKeel]

Sorry. Very Sorry I do not really know what I am doing.

Your answer worked when I type or paste in two different numbers in E4 and I4.

But

I have a problem

The first is that I was wrong in "just saying" I pasted a number into a cell linked to workbook sheet E4 and I4.
Actually, E4 and I4 both contain formulas that sum values pulled in from other worksheets. The macro you provided does not seem to work when comparing the formula results. Is there way it will compare the formula results displayed in E4 and I4 versus just when I type in a numerical value?