Excel VBA Course

(35% Sale Ends Jan. 26)

Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course (35% Discount)

How to capture the cell value crosses the target cell value

0

Hello everyone,

Hope you all are doing great.

I am looking for excel formula for whenever the cell value crosses the Target cell value, I get alert/ notified.

For Example, Let say, the current value is 100 in excel and the target value is 105.

Whenever the current value crosses the target value i.e. 105, i want to capture the same in excel or i want alert / notified for the same.

Attached excel sheet for your ready reference.

Can I request for help for the same.

Regards, 

Answer
Discuss

Discussion

Hi Sunil

Your question doesn't have sufficient detail for a proper answer to be given but I suspect you're looking for a VBA solution since Excel deals in static formulae otherwise.

How does the value change (e.g. is it a constantly rising daily production total or a varying production rate say) and how is the value change made (previously your spreadsheets were updated by remote processes e.g. SQL updates)? What do you mean by "an alert"- a timetsamp, a changed cell value, an email or what?

As usual, please edit your original question to advise the details (so it can be "read alone") and if possible attach a sample Excel file.
John_Ru (rep: 2867) Dec 14, '21 at 3:44 am
Sunil. Haven't seen a clear question from you on this so assume that you've fixed your problem. Alternatively it might be that Alerts aren't working currently (and you're not visiting the Forum frequently to check for answers)
John_Ru (rep: 2867) Dec 16, '21 at 12:06 pm
Hi, Edited the Original question. Please guide.
Thanks.
SunilA (rep: 40) Dec 27, '21 at 12:27 am
Sunil. Your question text seems much the same and doesn't address the specific questions I asked you about the manner in which the value is updated.or what you mean by "an alert" (also question detail should NOT be stated on attached files)
- see Rules / Question Etiquette).
I'm not aware of a formula to do what you ask. In previous answers you've been shown how to use VBA to capture max/min and compare values (which might trigger whatever alert you want).
John_Ru (rep: 2867) Dec 27, '21 at 2:16 am
Helo Sunil and John,

I thought I would take a stab at this. I searched the website and came across an answer you gave a year ago to a similar question from "Lisbeth". I used the code you wrote and modified it somewhat to hopefully solve Sunil's problem. I haven't tested it - I will leave that to your expertice. If I have goofed something up feel free to fix it as needed.
Sub NotificationEmail()
 
Dim OutApp As Object
Dim OutMail As Object
 
Application.ScreenUpdating = False
 
Set OutApp = CreateObject("Outlook.Application") 'enter the name of your email app
 
    If Range("C4").Value > Range("D4") Then
        Set OutMail = OutApp.CreateItem(olMailItem)
            With OutMail
            .To = "Your.EmailAddress@Outlook.com"  'change to your address
            .CC = ""
            .Subject = "New High Volume"
            .HTMLBody = "New High of" & Sheet1.Range("C4")
            .Close
            End With
    End If
 
Set OutMail = Nothing
Set OutApp = Nothing
 
Application.ScreenUpdating = True
 
End Sub


Cheers and Happy New Year
WillieD24 (rep: 120) Dec 28, '21 at 8:15 pm
Thanks Willie. At a quick glance it looks like that will work when the macro is launched (and you could have proposed it as Answer).

Even if the "alert" is an email (I've asked Sunil but got no reply), the problem I think is that Sunil doesn't want to run a macro to get a notificaton but to get one automatically. Furthermore from  I recall from his orevious wyestiond that the event triggering the macro might be a remote one (e.g. an worksheet update via SQL) rather than a manual change but again Sunil hasn't replied.

Neither of us should waste time solving an ill-defined question.

Happy New Year (soon) everyone! 
John_Ru (rep: 2867) Dec 29, '21 at 2:02 am
Thank you Willie and John for your precious time. I just require one simple formula to capture the value crossover the targeted value in excel itself.
SunilA (rep: 40) Jan 6, '22 at 5:04 am
Add to Discussion

Answers

0

Sunil,

In the discussion above (attempting to clarify your question), you request a "simple formula" (but I suspect that's not what you want). My guarded response is... 

If day high volume (target) is in D4 and current volume in C4 (and updated by some process), use:

=IF(C4>D4,"New high volume=" & C4 & " (manually replace D4)","")
then that cell will show a message ONLY while C4 is higher (so you will have to monitor it since it doesn't "capture" the highest value reached unless you overwrite D4 avidky)

My view is that (without VBA) the highs of a single cell like C4 can't be captured and D4 can't be updated automatically (since circular references would be created in a formula). 

Discuss

Discussion

Thanks for your response
SunilA (rep: 40) Jan 6, '22 at 8:25 am
Your given solution is working for me. Thanks John
SunilA (rep: 40) Jan 7, '22 at 10:01 am
That's good Sunil. Don't forget to select my Answer please. 
John_Ru (rep: 2867) Jan 7, '22 at 11:02 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login