Generating automatic pop-ups with multiple possilbe inputs and different mesasges

[meg93]

Hi, so I want to check 5 different cells on an excel worksheet against pre-defined rules. Some of the cells wont be populated, but I want any change to check against a condition a generate a pop-up. I've tried creating a VBA script for it, but can't get it to work:

Private Sub Worksheet_change(ByVal Target As Range)Dim FICO As Range
Dim CoFICO As Range
Dim Underwater As Range
Dim State As Range
Dim Units As Range
Set FICO = Intersect(Target, Range("$C$8"))
Set CoFICO = Intersect(Target, Range("$C$9"))
Set Underwater = Intersect(Target, Range("$C$13"))
Set State = Intersect(Target, Range("$C$17"))
Set Units = Intersect(Target, Range("$C$18"))
For Each FICO In FICO
    If FICO.Value < 500 Then MsgBox "FICO Minimum of 500 for both 1st Alliance and Montage"
    If Target.Value < 560 Then MsgBox "FICO Minimum of 560 needed for Montage"
Next FICO

For Each CoFICO In CoFICO
    If Target.Value = 500 Then MsgBox "FICO Minimum of 500 for both 1st Alliance and Montage"
    If Target.Value < 560 Then MsgBox "FICO Minimum of 560 needed for Montage"
Next CoFICO
For Each Underwater In Underwater
    If Target.Value <= 1 Then MsgBox "Borrower must be underwater"
Next
    
For Each State In State
    If Target.Value = "ID" Then MsgBox "ID excluded from First Alliance"
    If Target.Value = "MO" Then MsgBox "MO excluded from First Alliance"
    If Target.Value = "NV" Then MsgBox "NV excluded from First Alliance"
    If Target.Value = "WV" Then MsgBox "WV excluded from First Alliance"
    If Target.Value = "HI" Then MsgBox "HI excluded from First Alliance"
    If Target.Value = "MS" Then MsgBox "MS excluded from Montage"
Next
    
For Each Units In Units
    If Target.Value > 4 Then MsgBox "Fail: The maximum number of units allowed is 4"
Next
End Sub

Any help would be greatly appreciated.
Thanks!

 

[AlphaFrog]

You could something similar to this using just the Data Validation feature.

If you want to use code, try something like this...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
        
    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] Target.Address(0, 0)
        [COLOR=darkblue]Case[/COLOR] "C8"   [COLOR=green]'FICO[/COLOR]
            [COLOR=darkblue]If[/COLOR] Target.Value < 500 [COLOR=darkblue]Then[/COLOR] MsgBox "FICO Minimum of 500 for both 1st Alliance and Montage"
            [COLOR=darkblue]If[/COLOR] Target.Value < 560 [COLOR=darkblue]Then[/COLOR] MsgBox "FICO Minimum of 560 needed for Montage"
        
        [COLOR=darkblue]Case[/COLOR] "C9" [COLOR=#008000]'CoFICO[/COLOR]
            [COLOR=darkblue]If[/COLOR] Target.Value = 500 [COLOR=darkblue]Then[/COLOR] MsgBox "FICO Minimum of 500 for both 1st Alliance and Montage"
            [COLOR=darkblue]If[/COLOR] Target.Value < 560 [COLOR=darkblue]Then[/COLOR] MsgBox "FICO Minimum of 560 needed for Montage"
            
        [COLOR=darkblue]Case[/COLOR] "C13" [COLOR=green]'Underwater[/COLOR]
            [COLOR=darkblue]If[/COLOR] Target.Value <= 1 [COLOR=darkblue]Then[/COLOR] MsgBox "Borrower must be underwater"
            
        [COLOR=darkblue]Case[/COLOR] "C17"  [COLOR=green]'State[/COLOR]
            [COLOR=darkblue]If[/COLOR] Target.Value = "ID" [COLOR=darkblue]Then[/COLOR] MsgBox "ID excluded from First Alliance"
            [COLOR=darkblue]If[/COLOR] Target.Value = "MO" [COLOR=darkblue]Then[/COLOR] MsgBox "MO excluded from First Alliance"
            [COLOR=darkblue]If[/COLOR] Target.Value = "NV" [COLOR=darkblue]Then[/COLOR] MsgBox "NV excluded from First Alliance"
            [COLOR=darkblue]If[/COLOR] Target.Value = "WV" [COLOR=darkblue]Then[/COLOR] MsgBox "WV excluded from First Alliance"
            [COLOR=darkblue]If[/COLOR] Target.Value = "HI" [COLOR=darkblue]Then[/COLOR] MsgBox "HI excluded from First Alliance"
            [COLOR=darkblue]If[/COLOR] Target.Value = "MS" [COLOR=darkblue]Then[/COLOR] MsgBox "MS excluded from Montage"
        
        [COLOR=darkblue]Case[/COLOR] "C18" [COLOR=green]'Units[/COLOR]
            [COLOR=darkblue]If[/COLOR] Target.Value > 4 [COLOR=darkblue]Then[/COLOR] MsgBox "Fail: The maximum number of units allowed is 4"
            
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[meg93]

This is perfect, thank you! I added a ">0" for the first two input types so it doesn't pop-up when I delete the numbers.