popup based on two conditions

[projectile]

I have this vb script which basically states if a value in column G is less than 0 then show a messaage box as below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
If Target.Value < 0 Then
MsgBox "You have entered a negative figure - are you sure this is correct?"
End If
End If
End Sub

What I would like to do is extend this script so that another message box pops up based on two conditions

i.e. if a cell in column F shows string "accounts use only" AND value in the adjacent cell in column G is > 0 then MsgBox "you have entered a positive figure - are you sure this is correct?"

 

[p45cal]

try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
    If Target.Value < 0 Then MsgBox "You have entered a negative figure - are you sure this is correct?"
    If Target.Value > 0 And Target.Offset(, -1).Value = "accounts use only" Then MsgBox "you have entered a positive figure - are you sure this is correct?"
End If
End Sub

 

[Smitty]

How about:

If Target.Offset(,-1).Value = "accounts use only" And Target.Value > 0 Then...

HTH,

 

[hippiehacker]

I have this vb script which basically states if a value in column G is less than 0 then show a messaage box as below.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
If Target.Value < 0 Then
MsgBox "You have entered a negative figure - are you sure this is correct?"
End If
End If
End Sub

What I would like to do is extend this script so that another message box pops up based on two conditions

i.e. if a cell in column F shows string "accounts use only" AND value in the adjacent cell in column G is > 0 then MsgBox "you have entered a positive figure - are you sure this is correct?"

try

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
If Target.Value < 0 Then
MsgBox "You have entered a negative figure - are you sure this is correct?"
End If
If Range(Target.Address).Offset(0, -1).Value = "accounts use only" And Range(Target.Address).Offset(0, 0) > 0 Then
MsgBox "You have entered a positive figure - are you sure this is correct?"
End If
End If
End Sub

 

[projectile]

thank you p45cal that worked great!

I needed to add to it so that it works both ways if user entered the value first before enetering "accounts use only" see final code below

thanks everyone else for your efforts

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
If Target.Value < 0 Then MsgBox "You have entered a NEGATIVE figure - are you sure this is correct?"
If Target.Value > 0 And Target.Offset(, -1).Value = "Accounts use only" Then MsgBox "you have entered a POSITIVE figure - are you sure this is correct?"
End If
If Target.Value = "Accounts use only" And Target.Offset(, 1).Value > 0 Then MsgBox "you have entered a POSITIVE figure - are you sure this is correct?"
End Sub

 

[p45cal]

Whil;e "Accounts use only" is likely only to appear in column F it may be a bit more robust to confine that check to column F as you confined the previous check to column G:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Not Intersect(Target, Range("G3:G371")) Is Nothing Then
    If Target.Value < 0 Then MsgBox "You have entered a NEGATIVE figure - are you sure this is correct?"
    If Target.Value > 0 And Target.Offset(, -1).Value = "Accounts use only" Then MsgBox "you have entered a POSITIVE figure - are you sure this is correct?"
End If
[COLOR=#0000ff]If Not Intersect(Target, Range("F3:F371")) Is Nothing Then[/COLOR]
    If Target.Value = "Accounts use only" And Target.Offset(, 1).Value > 0 Then MsgBox "you have entered a POSITIVE figure - are you sure this is correct?"
[COLOR=#0000ff]End If[/COLOR]
End Sub