VBA Modification

[rhmkrmi]

Hi there,

I need help with this code that I have put together:

Private Sub worksheet_selectionchange(ByVal target As Range)
Static LastCell As Range

If Not LastCell Is Nothing Then
If LastCell.Column = 16 Then
If Not IsEmpty(LastCell.Offset(0, -1)) Then
If IsEmpty(LastCell) Then
MsgBox (" Cell cannot be blank.")
Application.EnableEvents = False
LastCell.Select
Application.EnableEvents = True
End If
End If
End If
End If

Set LastCell = ActiveCell

If Not LastCell Is Nothing Then
If LastCell.Column = 15 Then
If Not IsEmpty(LastCell.Offset(0, 1)) Then
If IsEmpty(LastCell) Then
MsgBox (" Cell cannot be blank")
Application.EnableEvents = False
LastCell.Select
Application.EnableEvents = True
End If
End If
End If
End If

Set LastCell = ActiveCell

End Sub

What I am trying to achieve is a pop-up if a cell in column O is blank but the cell in column P is not blank and vice versa.

It works when cell in column O is not blank and you try to leave cell in column P blank but it let's you leave cell in column O blank and populate cell in column P and go to the next row unless you go back to the cell in column O afterwards!

Or if you know of a better way to code this, please let me know.

Thanks.

 

[rhmkrmi]

Thanks a lot.

As soon as I enter a number in column D (regardless of what it starts with) it fails as shown below:

 

[JackDanIce]

Adjusted and tested, this works for me if the first character entered in column D is "4":

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim s   As String

    With Target
        If .CountLarge > 1 Or .Value = "" Then Exit Sub
        If .Column = 4 And Left$(.Value, 1) = .Column Then s = "Don't forget to fill column H"
        If .Column = 8 And Len(.Value & .Offset(, 1).Value) = Len(.Value) Then s = "Missing column name"
        If .Column = 9 And Len(.Value & .Offset(, -1).Value) = Len(.Value) Then s = "Missing column number"
    End With
  
    If s <> "" Then MsgBox s, vbInformation, "Enter missing details"
End Sub

 

[rhmkrmi]

Worked for me too, thank you very much.

 

[JackDanIce]

You're welcome, glad resolved