If two cells are different

DaleKeel

New Member
Joined
Sep 11, 2019
Messages
49
Office Version
2013
Platform
Windows
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
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,274
Office Version
2019, 2016, 2013
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,616
Office Version
365
Platform
Windows
@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
VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If [I4] <> [E4] Then ActiveWindow.Zoom = 200 Else ActiveWindow.Zoom = 100
End Sub
 

mole999

Moderator
Joined
Oct 23, 2004
Messages
10,274
Office Version
2019, 2016, 2013
Platform
Windows
@Yongle, would this not evaluate quicker
VBA Code:
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,616
Office Version
365
Platform
Windows
@mole999 , yes that would be better.
 

DaleKeel

New Member
Joined
Sep 11, 2019
Messages
49
Office Version
2013
Platform
Windows
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

New Member
Joined
Sep 11, 2019
Messages
49
Office Version
2013
Platform
Windows
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

New Member
Joined
Sep 11, 2019
Messages
49
Office Version
2013
Platform
Windows
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

Well-known Member
Joined
Mar 11, 2015
Messages
5,616
Office Version
365
Platform
Windows
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

VBA Code:
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

VBA Code:
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

New Member
Joined
Sep 11, 2019
Messages
49
Office Version
2013
Platform
Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,167
Messages
5,442,787
Members
405,196
Latest member
rmuir12

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top