Run a Macro when two cells are equal

[a-drift]

Hello,

I have been reading a lot about macros on MrExcel, and have found answers to 95% of what I'm trying to achieve. I hope this last 5% is an elegant solution...

I have developed a swing-trading model which downloads a time (in cell AR5 of the tab "DJIA Trade Trigger Calcs") and stock data each minute via a web-query, which then re-calculates trade triggers. In this same tab, I have a formula in cell AR15 that equals what I have already entered for a time (in cell A18 of the tab "E-mailTab") to trigger an automatic e-mail to a stock broker. Here's what I have entered in the "DJIA Trade Trigger Calcs" tab via the View Code selection:

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("AR5") = Range("AR15") Then
Application.EnableEvents = False
Application.ScreenUpdating = False
Call CDO_Mail_Small_Text
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "E-mail has been sent. System suspended for one minute..."
Application.Wait (Now + TimeValue("00:01:00"))
Call Update_IntraDay_Values
Else
End If

End Sub

This doesn't trigger when AR5 = AR15. Any help would be much appreciated.

 

[ColinKJ]

Hi a-drift,

I assume AR5 and AR15 are numeric value !!.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
If one or both values are calculated, there could be differences in the decimal results, while still displaying to the decimal places selected in formatting.<o></o>
<o></o>
How many decimal places of accuracy do you want.
<o></o>
ColinKJ

 

[a-drift]

My apologies. Neglected to mention that both dates are in the format "Real-time: 2:56PM EDT", ... not in an Excel date format.

 

[Gary McMaster]

<title>Change Event</title><style>@import url(/Office.css);</style><link disabled="" href="/MSOffice.css" type="text/css" rel="stylesheet"> Maybe the event is not firing?


Gary

<title>Change Event</title><style>@import url(/Office.css);</style><link disabled="" href="/MSOffice.css" type="text/css" rel="stylesheet">


Change Event (from help)



"This event doesn't occur when cells change during a recalculation. Use the Calculate event to trap a sheet recalculation. Deleting cells doesn't trigger this event".
<script language="VBScript" src="ShowMe_Code.vbs"></script> <script language="VBSCRIPT"> Sub HelpPopup (sFile, sID) Call InitConstants() On Error Resume Next r=oua.HelpPopup(sFile,sID) If Err<>0 Then Msgbox L_SECURITY_MSG,48,L_TITLE End Sub </script>

 

[a-drift]

The date text in cell AR5 gets there from the web-query, not a formula, so I assume that's ok.

The date text in cell AR15 is a formula that just copies from cell A18 of the tab "E-mailTab", so I experimented by changing the macro line from...

If Range("AR5") = Range("AR15") Then

to...

If Range("AR5") = Sheets("E-mailTab")Range("A18").Value Then

... but it still doesn't run when these two cells become equal.

 

[ColinKJ]

Hi,

You could try moving your code from the

Private Sub Worksheet_Change(ByVal Target As Range)

and put it in a standard moduel

at the start on the moduel add

Application.Calculate

Then at the end of your web-query, call the new moduel.

ColinKJ