Show msg box based on consecutive values

[Oximoxi]

Hi all,

I'm struggling with one problem. I have a spreadsheet, where numbers are filled into columns A,B,C in values from 1 to 15. And I'd like excel to show msg box based on theese two conditions:

1. if 3 consecutive rows contain number lower than 5
2. if 3 consecutive rows contain number higher than 10

Show msg based on the value is no problem, but I don't know how to deal with the consecutive condition.

Thanks for any help!

Oxi.

 

[Oximoxi]

Ideal solution for me would be, if the macro would be triggered by change of the last cell, the cell in C column. The macro would check wether the current row and two previous rows contain numbers <5 or >10 and if so, it would show the msg box.

 

[MickG]

Try this "Change_Event" Code:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
    [COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Rw [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, Ten, Five
 [COLOR="Navy"]If[/COLOR] Target.Count = 1 [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] Target.Row = ActiveSheet.UsedRange.Rows.Count And Not Intersect(Columns("A:C"), Target) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]Set[/COLOR] Rng = Cells(Target.Row - 2, 1).Resize(3, 3)
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] Rng.Rows
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Rw.Cells
                [COLOR="Navy"]If[/COLOR] R > 10 [COLOR="Navy"]Then[/COLOR]
                    Ten = Ten + 1
                    [COLOR="Navy"]Exit[/COLOR] For
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] R
         [COLOR="Navy"]Next[/COLOR] Rw
         [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] Rng.Rows
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] Rw.Cells
                [COLOR="Navy"]If[/COLOR] R < 5 [COLOR="Navy"]Then[/COLOR]
                    Five = Five + 1
                    [COLOR="Navy"]Exit[/COLOR] For
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] R
        [COLOR="Navy"]Next[/COLOR] Rw
        [COLOR="Navy"]If[/COLOR] Ten = 3 [COLOR="Navy"]Then[/COLOR] MsgBox "Each row of Range " & Rng.Address & vbLf & " has Values Greater than Ten"
        [COLOR="Navy"]If[/COLOR] Five = 3 [COLOR="Navy"]Then[/COLOR] MsgBox "Each row of Range " & Rng.Address & vbLf & " has Values Less Than Five"
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Oximoxi]

Thanks guys for help!
But unfortunately, the Mick's code does not work either. I copied it to blank excel file to test it out, but when I paste it into new module, I can't run it.

 

[MickG]

It need to be paste in the Activesheet module (The one with the data).
Right click sheet tab, select "View Code" , VB window appears , paste code into this window.
The code runs when a value is entered into the last row of columns "A to C".
Try this example, code slightly modified !!
https://app.box.com/s/482nfckuzk5e98zwpu9xv1imgctdkrdg

 

[Oximoxi]

I see...
Great, now it works, thank you!
Just one more thing: would it be possible to trigger the macro only after change in the last cell of the row (change in C column)? So that the msg box appears only when you enter all data.
Thanks again!

 

[MickG]

You're welcome
For Last cell column "C" activation:-
Change this line

If Target.Row = ActiveSheet.UsedRange.Rows.Count And Not Intersect(Columns("A:C"), Target) Is Nothing Then

To this line

    If Target.Row = ActiveSheet.Cells(1).CurrentRegion.Rows.Count And Target.Column = 3 Then