Conditional Alert Excel

cpatte7372
cpatte7372 used Ask the Experts™
on
Hello Experts,

I have three separate requests that are related. However, I would like to deal with them individually. So, I'm not sure if I should submit three different requests or just wait until one question has been answered and then simply follow on with the other questions? I guess you'll let me know.

The first request to ask if someone could help me write some kind of alert that will sound when a certain conditions occurs.

You will see from the attached spreadsheet that we have four cells highlighted Green in rows 3, 12, 20 and 27. I would very much appreciate if someone could compile a formula that will alert me when any  four cells, as described are highlighted green?

As always, I appreciate your help.

Cheers
EEMINI-DOW.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Experts,

It would also be very help if the spreadsheet had a separate column letting me know what symbols are alerting.

So, in the illustration the following symbols could be placed into a separate column:

YM #F
KO
DIS
MSFT.

This method might even make it easier for any expert willing to assist.

Just so you know, the symbols represent companies - have a guess what MSFT stands for :-)

Author

Commented:
Experts

Please let me know if you need further clarification with the request?

Cheers

Carlton

Commented:
What kind of alert are your talking about? A pop-ip message box, for instance, telling you the rows where all of the values on columns AY, AZ, BB and BD, from the same row, are green?
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Author

Commented:
jppinto,

Thanks for responding. I was thinking more on the lines of say a popup alert letting me know that YM #F
YM #F, KO, DIS, MSFT are the four symbols that matched green for the four cells highlighted in the illustration.

Is that possible?

Cheers mate..
Try this sub. This is an event trapper and you should insert it in the code pane of the relevant worksheet. To do that right-click on the sheet name and click on "View code" and then past the code.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then
    For Each rw In Target.Rows
        Call Worksheet_Change(rw.Cells(1, 1))
    Next rw
    Exit Sub
End If
Dim cel As Range
For Each cel In Range("$AY$" & Target.Row & ":$AZ$" & Target.Row & ",$BB$" & Target.Row & ",$BD$" & Target.Row)
    For Each cf In cel.FormatConditions
        frmla = cf.Formula1
        frmla = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
        frmla = Application.ConvertFormula(frmla, xlR1C1, xlA1, , cel)
        If Evaluate(frmla) Then
            If cf.Interior.Color <> 52377 Then Exit Sub
            stts = "green"
            Exit For
        End If
    Next cf
    If stts <> "green" Then Exit Sub
    stts = ""
Next cel
MsgBox "All cells are green"
End Sub

Open in new window

Author

Commented:
Hi ssaqibh

Thanks responding,

I entered the code but I didn't get any pop up message

Author

Commented:
ssaqibh

As you could from the from the sample spreadsheet, I have four conditions that meet the requirements. Did you get a pop up message?


Cheers
Actually this is an event based routine and will fire if any such row is changed where the cells are green.

I am now working on one for all rows.

Author

Commented:
Thanks ssaqibh:
Here you go
Sub checkall()
Set ws = Sheets("mini sized DOW")
For Each rw In ws.Range("A3:A" & Range("A3").End(xlDown).Row)
For Each cel In Range("$AY$" & rw.Row & ":$AZ$" & rw.Row & ",$BB$" & rw.Row & ",$BD$" & rw.Row)
    For Each cf In cel.FormatConditions
        frmla = cf.Formula1
        frmla = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
        frmla = Application.ConvertFormula(frmla, xlR1C1, xlA1, , cel)
        If Evaluate(frmla) Then
            If cf.Interior.Color <> 52377 Then Exit For
            stts = "green"
            Exit For
        End If
    Next cf
    If stts <> "green" Then Exit For
Next cel
If stts = "green" Then cplist = cplist & rw.Cells(1, 2) & ", "
stts = ""
Next rw
MsgBox "All cells are green for" & vbCrLf & Left(cplist, Len(cplist) - 2)
End Sub

Open in new window

Hang on. not yet correct.
Corrected
Sub checkall()
Set ws = Sheets("mini sized DOW")
For Each rw In ws.Range("A3:A" & Range("A3").End(xlDown).Row)
For Each cel In Range("$AY$" & rw.Row & ":$AZ$" & rw.Row & ",$BB$" & rw.Row & ",$BD$" & rw.Row)
    For Each cf In cel.FormatConditions
        frmla = cf.Formula1
        frmla = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
        frmla = Application.ConvertFormula(frmla, xlR1C1, xlA1, , cel)
        If Evaluate(frmla) Then
            If cf.Interior.Color <> 52377 Then stts = "": Exit For
            stts = "green"
            Exit For
        End If
    Next cf
    If stts <> "green" Then Exit For
Next cel
If stts = "green" Then cplist = cplist & rw.Cells(1, 2) & ", "
stts = ""
Next rw
MsgBox "All cells are green for" & vbCrLf & Left(cplist, Len(cplist) - 2)
End Sub

Open in new window

Author

Commented:
ssaqibh

Thanks again for assisting me with this, however it I paste the formula in but still no message box.....
Lets start again.

Open the file you uploaded
Right-click on the sheet name and click on view code
then    Insert > module
paste the code in this window
close the vba window
From excel press Alt-F8
select Checkall
press run

Author

Commented:
ssaqibh

Did you manage to get it to work with the sample spreadsheet?
If still struggling then try this file.

Open the file
Press Alt-F8
select Checkall
Click run
Copy-of-Xl0000033.xls

Author

Commented:
Oh I see...

I was hoping that the message would pop up when the condition occurred in real-time... Just one second while i run it...

Author

Commented:
ssaqibh

I'm getting the message 'Invalid procedure call or argument'
In my file or in your file?
For "real time" try my first code.

Author

Commented:
Oh, I didn't try your file.... One second..

Author

Commented:
ssaqibh

In your file I get the error message 'Type Mismatch'
Which line?
I am running this on 2003. I wonder whether it could be different on a later version.

Author

Commented:
It doesn't actually say what line.

I'm runnning XP
Can you give me a screenshot

Author

Commented:
ssaqibh

Its odd. With you file I get a 'Type Mismatch', when I paste the code in my file I get 'Invalid procedure call or argument'

Author

Commented:
ssaqibh

Sure. What do you want a screenshot of?
The error states in both files.

Author

Commented:
Here is the error message in my file
MyFile.jpg

Author

Commented:
Your file...


ssaqibhfile.jpg
What happens when you click on OK?

One last attempt before I go to bed. If it still does not work I shall try it in the office after I wake up. Upload your file with the macro

Author

Commented:
Here you go
EEMINI-DOW.xlsm

Author

Commented:
When I click ok nothing happens
Ok here is one worked on 2010. There had to be a number of changes. It is a pity there are so many inconsistencies between versions.

Saqib
Sub checkall()
Set ws = Sheets("mini sized DOW")
For Each rw In ws.Range("A3:A" & Range("B3").End(xlDown).Row)
For Each cel In Range("$AY$" & rw.Row & ":$AZ$" & rw.Row & ",$BB$" & rw.Row & ",$BD$" & rw.Row)
    For Each cf In cel.FormatConditions
        frmla = cf.Formula1
        frmla = Application.ConvertFormula(frmla, xlA1, xlR1C1, , cf.AppliesTo.Cells(1, 1))
        frmla = Application.ConvertFormula(frmla, xlR1C1, xlA1, , cel)
        If Evaluate(frmla) Then
            If cf.Interior.Color <> 5296274 Then stts = "": Exit For
            stts = "green"
            Exit For
        End If
    Next cf
    If stts <> "green" Then Exit For
Next cel
If stts = "green" Then cplist = cplist & rw.Cells(1, 2) & ", "
stts = ""
Next rw
MsgBox "All cells are green for" & vbCrLf & Left(cplist, Len(cplist) - 2)
End Sub

Open in new window

Author

Commented:
Ss

Thanks for your continued help with this. I will put it to the testbin 10 mins.

Cheers
testbin?

Author

Commented:
ssaqibh

You've nailed it mate.... works like a dream.

Can't thank you enough.

Cheers mate..

Author

Commented:
ssaqibh,

Just one thing if I may. Would it be possible for the message box to automatically appear without having to run the macro? So, may be the macro could run itself when the conditions occur?

Cheers

Author

Commented:
ssaqibh,

As I mentioned at the beginning of this request I have three questions. I would like to use the formula you have kindly created to build on another request I have. So I'm going to assign the points and open another question.
In the meantime, I would like thank you very much.

Cheers mate..

Author

Commented:
Fantastic.
There may be a few ways this could be done.

- Any change to the worksheet and the message pops up. Too much of a nuisance.
- Any change to the worksheet and only that particular row is checked and message popped accordingly.

Both above methods do not take into account the status before making the change. So if they were green previously and remain green after the change the box will always pop up. I don't think this is what you are looking for.

To keep track of what was there previously requires much more programming and I am not sure if I can complete it here.

If you are looking for any of the first two options just say so and I shall modify the code to suit.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial