?
Solved

Conditional Formatting with Excel - Part 2 With An Alert

Posted on 2011-02-15
30
Medium Priority
?
365 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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
 

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

719 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