New Excel Alert Question
Experts,
You will see from the sample spreadsheet I have configured conditional alerts in column Q to highlight the cell when values match with those in column D. I would like to add an alert to the condition.
Fernando and Brian have effectively written a formula to accomplish this I would just like to have it applied to spreadsheet.
If you need information on how they accomplished it let me know.
Cheers
One example included the formula, however Fernando's method seemed to work very well.
P.S.
Please be mindful that the values in column D are stock prices and change virtually every second.
Option Explicit
Private Sub Worksheet_Calculate()
Dim xCell As Range
Dim xLastRow As Long
Dim xValue As Variant
xLastRow = ActiveSheet.Range("A1").Sp
If xLastRow < 2 Then Exit Sub
For Each xCell In Range("O2:O" & xLastRow)
xValue = xCell.Value
If Not IsError(xValue) Then
If xValue <> "" And xValue < 0.0002 Then
Beep
Cells(1, 15).Interior.Color = 255
Exit Sub
End If
End If
Next
Cells(1, 15).Interior.Color = 65535
End Sub
Trade-Day-sample.xlsm
You will see from the sample spreadsheet I have configured conditional alerts in column Q to highlight the cell when values match with those in column D. I would like to add an alert to the condition.
Fernando and Brian have effectively written a formula to accomplish this I would just like to have it applied to spreadsheet.
If you need information on how they accomplished it let me know.
Cheers
One example included the formula, however Fernando's method seemed to work very well.
P.S.
Please be mindful that the values in column D are stock prices and change virtually every second.
Option Explicit
Private Sub Worksheet_Calculate()
Dim xCell As Range
Dim xLastRow As Long
Dim xValue As Variant
xLastRow = ActiveSheet.Range("A1").Sp
If xLastRow < 2 Then Exit Sub
For Each xCell In Range("O2:O" & xLastRow)
xValue = xCell.Value
If Not IsError(xValue) Then
If xValue <> "" And xValue < 0.0002 Then
Beep
Cells(1, 15).Interior.Color = 255
Exit Sub
End If
End If
Next
Cells(1, 15).Interior.Color = 65535
End Sub
Trade-Day-sample.xlsm
cpatte7372,
OK, I'm feeling guilty that you're behaving like a gentleman and still have no answer. So strictly on condition that, if this suggestion is acceptable, you don't give me any points, please see attached.
Couple of points...
(1) This still has the original problem of excessive beeping. Worse, the problem is exacerbated as there are now two sets of conditions that will trigger it.
(2) It looks to me that there's a problem in the new conditional format. It assumes that the Column D values have two decimal places, but look at D4 (3.705). So, instead of "=D2", the conditional format should read "=round(D2,2).
(3) Following on from (2), to be on the safe side I've rounded both values in the attached formula.
(4) To slightly speed up the macro, is there any chance you could reduce the no. of rows in the sheet? There are lots of blanks rows below 102 and the values at the bottom of the sheet don't appear to be needed. And could the "#n/a#" rows go or at least be reduced?
Regards,
Brian.
OK, I'm feeling guilty that you're behaving like a gentleman and still have no answer. So strictly on condition that, if this suggestion is acceptable, you don't give me any points, please see attached.
Couple of points...
(1) This still has the original problem of excessive beeping. Worse, the problem is exacerbated as there are now two sets of conditions that will trigger it.
(2) It looks to me that there's a problem in the new conditional format. It assumes that the Column D values have two decimal places, but look at D4 (3.705). So, instead of "=D2", the conditional format should read "=round(D2,2).
(3) Following on from (2), to be on the safe side I've rounded both values in the attached formula.
(4) To slightly speed up the macro, is there any chance you could reduce the no. of rows in the sheet? There are lots of blanks rows below 102 and the values at the bottom of the sheet don't appear to be needed. And could the "#n/a#" rows go or at least be reduced?
Regards,
Brian.
Option Explicit
Private Sub Worksheet_Calculate()
Dim xCell As Range
Dim xLastRow As Long
Dim xValue As Variant
Dim xValue2 As Variant
Dim Point02 As Boolean
xLastRow = ActiveSheet.Range("A1").SpecialCells(xlLastCell).Row
If xLastRow < 2 Then Exit Sub
For Each xCell In Range("O2:O" & xLastRow)
xValue = xCell.Value
If Not IsError(xValue) Then
If xValue <> "" And xValue < 0.0002 Then
Beep
Cells(1, 15).Interior.Color = 255
Point02 = True
Exit For
End If
End If
Next
If Not Point02 Then Cells(1, 15).Interior.Color = 65535
For Each xCell In Range("q2:q" & xLastRow)
xValue = xCell.Value
xValue2 = xCell.Offset(0, -13).Value
If Not IsError(xValue) And Not IsError(xValue2) Then
If xValue <> "" And xValue2 <> "" And Round(xValue, 2) = Round(xValue2, 2) Then
Beep
Cells(1, 17).Interior.Color = 255
Exit Sub
End If
End If
Next
Cells(1, 17).Interior.Color = 65535
End Sub
cpatte7372,
A suggestion about the excessive beeping.
There are two extremes - the damn thing beeps all the time or you never get an audio warning. Ideally, you want a beep each time a new occurrence triggers one of the conditions and my concern with this was that the continual checking might interfere with updates from the external application.
What about a button on the sheet which would check whether a condition had been met since the last time you either opened the file or pressed the button? The advantages are...
- Processing overhead would be incurred only when you hit the button rather than continuously.
- It avoids the problem of missing a "one-time" beep when you step over to the coffee machine.
[By the way, if anyone is wondering why my previous post required "no points" for me, it's because it was I who suggested that cpatte7372 open this as a new question.]
Regards,
Brian.
A suggestion about the excessive beeping.
There are two extremes - the damn thing beeps all the time or you never get an audio warning. Ideally, you want a beep each time a new occurrence triggers one of the conditions and my concern with this was that the continual checking might interfere with updates from the external application.
What about a button on the sheet which would check whether a condition had been met since the last time you either opened the file or pressed the button? The advantages are...
- Processing overhead would be incurred only when you hit the button rather than continuously.
- It avoids the problem of missing a "one-time" beep when you step over to the coffee machine.
[By the way, if anyone is wondering why my previous post required "no points" for me, it's because it was I who suggested that cpatte7372 open this as a new question.]
Regards,
Brian.
ASKER
Hi Brian,
How's it going mate?
Just got back from a pretty long weekend, and no time to check the any responses to this question.
I have now read and applied your suggestions. As you're aware this cannot be really tested until the markets open. However, I think having a button on the sheet would be absolutely ideal.
Can you show me how add a button to the sheet?
Cheers mate.
How's it going mate?
Just got back from a pretty long weekend, and no time to check the any responses to this question.
I have now read and applied your suggestions. As you're aware this cannot be really tested until the markets open. However, I think having a button on the sheet would be absolutely ideal.
Can you show me how add a button to the sheet?
Cheers mate.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Brian,
You must think how rude I am for not gettng back to you after you've taken time out to help me!
I'm sorry for not getting back to you, I've been incredibly busy lately.
Anyway, when I attempt to open the file I keep on getting the message 'Compile error: variable not defined: xvalue1
You must think how rude I am for not gettng back to you after you've taken time out to help me!
I'm sorry for not getting back to you, I've been incredibly busy lately.
Anyway, when I attempt to open the file I keep on getting the message 'Compile error: variable not defined: xvalue1
cpatte7372,
Oops, my mistake. I wrote the code for the "Trading (2)" sheet, I don't know how that "fossil" code ended up in "Trading". Just delete it from there.
Cheers,
Brian.
Oops, my mistake. I wrote the code for the "Trading (2)" sheet, I don't know how that "fossil" code ended up in "Trading". Just delete it from there.
Cheers,
Brian.
ASKER
Brilliant
Thanks, cpatte7372. (Although you weren't supposed to give me any points!)
ASKER
For those of you willing help, I need pop out for a bit. I will respond when I get back to me desk
Cheers