how do I: launch a macro if two cells do not equak one another?

  • Thread starter Thread starter jasonsweeney
  • Start date Start date
J

jasonsweeney

Its late in the day and I am frustrated not getting this simple
procedure to work:

I need a macro to fire when two cells in two different worksheets do
nto equal one another. Here is my code:

(that does not work....nothing happens at all)
_________
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Source As Range)

If Worksheets("Sheet1").Range("A2").Value =
Worksheets("Sheet2").Range("D2").Value Then
'
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "values do not match."
Style = vbOKOnly
Title = "No Match"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'
'
[lauches new sub routine]
End If
_________________

Do I have the wrong event or something?!
 
jason,

That event will only fire if something on the worksheet is changed.
What are you changing on the worksheet?
Is there something else in your code where you could call this
procedure (based on your other thread)??
Within your macro, just check the cells???

There are other events that you might use.
A good listing of them can be found here:
http://www.cpearson.com/excel/events.htm

John
 
John,

Lets assume Cell #1 has the value "3" in it.

Lets also assume Cell #2(different sheet) also has the value "3" i
it.

I am concerned when a user changes the value of Cell #1 to anythin
other than "3". Thus, when a user click in the cell, and changes th
value of Cell #1 to "1", I want the macro to fire once they pres
enter, or click out of the cell. Thus, I assumed Worksheet change wa
the correct event. But nothing happens in my code.....
 
jasonsweeney > said:
John,

Lets assume Cell #1 has the value "3" in it.

Lets also assume Cell #2(different sheet) also has the value "3" in
it.

I am concerned when a user changes the value of Cell #1 to anything
other than "3". Thus, when a user click in the cell, and changes the
value of Cell #1 to "1", I want the macro to fire once they press
enter, or click out of the cell. Thus, I assumed Worksheet change was
the correct event. But nothing happens in my code......
Click to expand...
 
jason,

The light bulb just went off in my head...sorry.

You need the Worksheet_Change Event.

THis needs to be placed in the worksheet code on the sheet
where you want to capture the change to that sheet. (Right click
on the sheet tab and select "View Code"

Private Sub Worksheet_Change(ByVal Target As Range)
If Worksheets("Sheet1").Range("A2").Value = _
Worksheets("Sheet2").Range("D2").Value Then
MsgBox "They Match"
Else
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "values do not match."
Style = vbOKOnly
Title = "No Match"
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
'[lauches new sub routine]
End If
End Sub

John
 
Back
Top