Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

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
0
cpatte7372
Asked:
cpatte7372
  • 18
  • 9
  • 3
1 Solution
 
jppintoCommented:
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
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
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
Industry Leaders: 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!

 
jppintoCommented:
Here you go...

jppinto
conditional-format-with-alerts.xlsm
0
 
jppintoCommented:
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
 
cpatte7372Author Commented:
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
 
jppintoCommented:
When the message is displayed, there is already a sound...at least on my computer ...:)

Is it OK for you?
0
 
cpatte7372Author Commented:
jppinto

No sound for me.

0
 
jppintoCommented:
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
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
jppinto,

Thanks ever-so-much mate....

0
 
cpatte7372Author Commented:
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
 
jppintoCommented:
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
 
cpatte7372Author Commented:
jppinto,

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

0
 
jppintoCommented:
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
 
cpatte7372Author Commented:
Oops!

Sound system wasn't enabled....

jppinto, thanks again mate..

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

Cheers

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

My sound system wasn't enabled.

I've got the beep and everything.

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


Carlton
0
 
cpatte7372Author Commented:
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
 
jppintoCommented:
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
 
cpatte7372Author Commented:
jppinto,

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

Cheers

0
 
cpatte7372Author Commented:
jppinto,

Thanks again mate.

Question closed
0
 
cpatte7372Author Commented:
Brilliant
0
 
redmondbCommented:
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
 
cpatte7372Author Commented:
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
 
cpatte7372Author Commented:
Redmond,

Thats what I'm talking about.

U D Man.....

Cheers mate.
0
 
redmondbCommented:
cpatte7372,

Glad to help!

Regards,
Brian.
0
 
cpatte7372Author Commented:
Brian,

I guess its too late to award you the points?
0
 
cpatte7372Author Commented:
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
 
redmondbCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 18
  • 9
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now