Solved

Conditional Formatting with Excel - Part 2 With An Alert

Posted on 2011-02-15
30
321 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:cpatte7372
  • 18
  • 9
  • 3
30 Comments
 
LVL 33

Expert Comment

by:jppinto
ID: 34899379
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

0
 

Author Comment

by:cpatte7372
ID: 34899584
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
0
 

Author Comment

by:cpatte7372
ID: 34899606
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
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34899627
Here you go...

jppinto
conditional-format-with-alerts.xlsm
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34899642
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

0
 

Author Comment

by:cpatte7372
ID: 34899691
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..
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34899713
When the message is displayed, there is already a sound...at least on my computer ...:)

Is it OK for you?
0
 

Author Comment

by:cpatte7372
ID: 34899773
jppinto

No sound for me.

0
 
LVL 33

Expert Comment

by:jppinto
ID: 34899811
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

0
 

Author Comment

by:cpatte7372
ID: 34899815
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
0
 

Author Comment

by:cpatte7372
ID: 34900234
jppinto,

Thanks ever-so-much mate....

0
 

Author Comment

by:cpatte7372
ID: 34900377
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
0
 
LVL 33

Accepted Solution

by:
jppinto earned 500 total points
ID: 34900537
You have to put the code on the TRADING sheet code window, not on a module! The event that is triggered is "Worksheet_Change", if you put the code on a module, it doesn't reference any sheet.

jppinto
Capturar.JPG
0
 

Author Comment

by:cpatte7372
ID: 34900684
jppinto,

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

0
 
LVL 33

Expert Comment

by:jppinto
ID: 34900761
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...
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:cpatte7372
ID: 34900863
Oops!

Sound system wasn't enabled....

jppinto, thanks again mate..

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

Cheers

Carlton
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34900883
Sorry that I can't help you more on the question of the beep but on my computer everything souds fine...
0
 

Author Comment

by:cpatte7372
ID: 34900933
jppinto,

My sound system wasn't enabled.

I've got the beep and everything.

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


Carlton
0
 

Author Comment

by:cpatte7372
ID: 34901052
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
0
 
LVL 33

Expert Comment

by:jppinto
ID: 34901096
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.....
0
 

Author Comment

by:cpatte7372
ID: 34901100
jppinto,

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

Cheers

0
 

Author Comment

by:cpatte7372
ID: 34901188
jppinto,

Thanks again mate.

Question closed
0
 

Author Closing Comment

by:cpatte7372
ID: 34901224
Brilliant
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34901702
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

0
 

Author Comment

by:cpatte7372
ID: 34902811
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
0
 

Author Comment

by:cpatte7372
ID: 34902821
Redmond,

Thats what I'm talking about.

U D Man.....

Cheers mate.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34903169
cpatte7372,

Glad to help!

Regards,
Brian.
0
 

Author Comment

by:cpatte7372
ID: 34905482
Brian,

I guess its too late to award you the points?
0
 

Author Comment

by:cpatte7372
ID: 34907167
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 34912311
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.

 
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now