vba code

[kroters]

i am after setting up a pop up box when 2 conditions are met.
example- i have shift times on a worksheet ranging from 0700-1630 to 0930-1900.
a cell above the shift times can be changed to 8.5 if they want the holiday.
what i want is a pop up box to show if 8.5 is added to a 0930-1900 day so they know they need to cover their shift.


a b c d e
1 8.5 8.5
2 0700-1630 0700-1630 0700-1630 0700-1630 0700-1630
3 8.5
4 0930-1900 0930-1900 0930-1900 0930-1900 0930-1900
5
6 0800-1730 0800-1730 0800-1730 0800-1730 0800-1730

 

[mrmmickle1]

Maybe try something like this:

[COLOR=#0000ff]Private Sub[/COLOR] Worksheet_Change([COLOR=#0000ff]ByVal[/COLOR] Target[COLOR=#0000ff] As [/COLOR]Range)


    [COLOR=#0000ff]If[/COLOR] Target.Value = "8.5" [COLOR=#0000ff]And [/COLOR]Target.Offset(1).Value = "0930-1900" [COLOR=#0000ff]Then[/COLOR]
        MsgBox "You will need to cover your shift!", vbCritical, "Alert!"
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[kroters]

worked a treat.many thanks.

 

[Peter_SSs]

I would strongly recommend adding the blue lines below. Otherwise, if anybody ever alters more than 1 cell at a time (eg select 2 cells & delete their contents, or copy/paste multiple cells etc) the code will error.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count = 1 Then
    If Target.Value = "8.5" And Target.Offset(1).Value = "0930-1900" Then
      MsgBox "You will need to cover your shift!", vbCritical, "Alert!"
    End If
  End If
End Sub