Link to home
Start Free TrialLog in
Avatar of cpatte7372
cpatte7372Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Conditional Formatting with Excel - Part 2 With An Alert

Hello Experts,

Barryhoudini helped me add a conditional format to a cell, I was now wondering if it's possible to add an alert along with the conditional format?

If its not possible can someone please show me other ways to add an alert when a condition occurs?

Cheers

Carlton
Avatar of jppinto
jppinto
Flag of Portugal image

You can add a macro to check if a particular range of cells where changed and if one of the cells has some value, then display a message box to the user, like this.

jppinto
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, [A1]) Is Nothing Then
      ' Cell A1 was changed, check the value of the cell
      If Target.Value>10 Then
         MsgBox "The value is greater that 10", vbCritical
      End If
   End If
End Sub

Open in new window

Avatar of cpatte7372

ASKER

jppinto,

That sounds fantastic.

I don't want to sound like I'm not prepared to help myself, however I attached a sample of the spreadsheet where I would need your formula.

You will see in column O a set of numbers that will turn orange if the value falls below 0.02% (This is what an expert helped me with)

Can you please show me how to apply the formula you suggested to the spreadsheet?

I would be very grateful.

Cheers
conditional-format-with-alerts.xlsx
jppinto,

I tried applying the formula myself but I couldn't get it to work. My excel programming is limited. Your help will be greatly appreciated.

Cheers
The macro is looking at range O2:O9
If you want to expand to other cells, just change the range on the code. Alt+F11 will take you to the Visual Basic Editor.

jppinto
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, [O2:O9]) Is Nothing Then 
      If Target.Value < 0.0002 Then
         MsgBox "The value is less than 0,02%", vbCritical
      End If
   End If
End Sub

Open in new window

jppinto,

That is brilliant mate.

Can I be a bit cheeky and ask if its possible to also get it with a sound alert?

Cheers mate..
When the message is displayed, there is already a sound...at least on my computer ...:)

Is it OK for you?
jppinto

No sound for me.

Just add the Beep before the message box, like this...

jppinto
Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Target, [O2:O9]) Is Nothing Then
     
      If Target.Value < 0.0002 Then
         Beep
         MsgBox "The value is less than 0,02%", vbCritical
      End If
   End If
End Sub

Open in new window

jppinto,

I've just got to pop out for a 10minutes. If you're able to help me with sound I'll check it out when I get back.

Really appreciate your help mate.

Carlton
jppinto,

Thanks ever-so-much mate....

jppinto,

I tried adding your formula to the one I'm currently using for trading but it won't work.

Any suggestions on what I'm doing wrong?
Trade-Day-15-02-11v2.xlsm
ASKER CERTIFIED SOLUTION
Avatar of jppinto
jppinto
Flag of Portugal image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
jppinto,

That worked. However, I still can't get it to beep or make any sound....

Is this the only application (Excel) where you don't get any sound/beeps? Do you have your system sound enabled?

The problem is on your Windows, not in Excel. Mine beeps when I get the message. Please check it...
Oops!

Sound system wasn't enabled....

jppinto, thanks again mate..

This has helped me out immensly with my trading....

Cheers

Carlton
Sorry that I can't help you more on the question of the beep but on my computer everything souds fine...
jppinto,

My sound system wasn't enabled.

I've got the beep and everything.

I can't thank you enough mate....


Carlton
jppinto,

How can I apply the formula to the complete column?

I've noticed that its working only for a few cells in the column.

Cheers
On my 3rd post (from the 8 I've posted!), I told you how to change it:

"If you want to expand to other cells, just change the range on the code".

Hope that this closes the question.....
jppinto,

Don't worry about it mate. I realise that I just needed to increase the column length.

Cheers

jppinto,

Thanks again mate.

Question closed
Brilliant
Avatar of redmondb
jppinto,

Hi. Congrats on the points!

If you don't mind, I had a couple of queries about the answer.
 - The OP has formulas in column O. So, if he changes one of those cells' precedents, the value in O will change, but your check won't see it.
 - If the use changes multiple cells (e.g. a paste) then, at best, only the first change will be checked.

So, would the change to your code below help?

Apologies if I'm missing the obvious,
Brian.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim xCell As Range

For Each xCell In Target
 If Cells(xCell.Row, 15) < 0.0002 Then
      MsgBox "Row " & xCell.Row & "'s value is less than 0.02% [" & Format(Cells(xCell.Row, 15).Value, "0.00%") & "]", vbCritical
   End If
Next

End Sub

Open in new window

Redmond,

You're spot on.

I was just checking the formula the way you suggested and it doesn't work.

I was literally about to ask if jppinto was still here so as to take another look at it.

I will check out your formula and see if that works.

Back in 5mins

Cheers
Redmond,

Thats what I'm talking about.

U D Man.....

Cheers mate.
cpatte7372,

Glad to help!

Regards,
Brian.
Brian,

I guess its too late to award you the points?
Brian,

I'm using your formula in real-time (while the markets are open) and when a change occurs I'm not presented with the alert. It works if you manually change the numbers, however when the numbers change automatically the alert doesn't appear.

I will also ask a related question, but if you do see this can you help?

Cheers
cpatte7372,

(1) Thanks, but points are already allocated! Win some, lose some - I've been stuck with points in the past that I didn't think I deserved.

(2) I need to know what's changing the spreadsheet. I've asked a question in the related question, so don't bother replying here.

Regards,
Brian.