Change Event based on two other cell values

myColumnOne = Range("NPN").Column 'this is column B, NPN is a Named Range of B1
myColumnTwo = Range("NPCH").Column 'this is column E, NPCH is a Named Range of E1

using these variables I want to say

When data is entered into any cell in myColumnOne
first check to see if this same data already exists in myColumnOne
if it does then check to see if in the row where the data already exists, if the corresponding cell in myColumnTwo ISBLANK then MsgBox

if the corresponding cell in myColumnTwo is not blank, allow the data to be entered.


Example:
Col B….Col E
ABC…..xxx
XZY…..

It will allow ABC to be entered because Column E is populated
It will not allow XYZ to be entered because Column E is blank, rather it will give a MsgBox alerting the user that the data already exists.

Thank you, I've never done a Change Event before

added & edited:

I should also add that the data being entered into myColumnOne will be entered into the last cell (first blank) in the column.
And that data may exist multiple times in the preceding cells, so each instance must be checked.

I'm thinking that a For Next Loop to check the preceding cells for the data and a Offset to check the corresponding cell in myColumnTwo is what's needed. But then if it was some kind of a Do Until Loop, it would stop when it found a blank cell in myColumnTwo.

Lastly, if it's even possible, it would find the existing data with the blank cell much faster if it searched from the bottom up instead of from row 1 down.

try this and see if it does what you need.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Dim LastUsedRow As Long
Dim NewEntry As String
Dim BlankFound As Boolean
Dim FNDBlnkRWS As String
Dim i As Long ' Used for stepping thorugh rows.
BlankFound = False 'set flag false to start

FNDBlnkRWS = ""
NewEntry = Target.Value

        If NewEntry <> "" Then
        
            LastUsedRow = Sheets(1).Range("B65336").End(xlUp).Row
            
            For i = LastUsedRow - 1 To 1 Step -1
                    If Cells(i, 2).Value = NewEntry Then
                    
                                If Cells(i, 5).Value = vbNullString Then
                                       FNDBlnkRWS = FNDBlnkRWS & i & " , " ' records corresponding row numbers with blanks
                                        
                                        BlankFound = True
                                    
                                'Exit For ' optional if you don't want to search for mulitple corresponding blanks in col E
                                Else
                                                      
                                End If
                      
                    End If
             Next i
             
             
                             If BlankFound = True Then
                                    Target.Clear ' clears entry in column be if another exists with corresponding blank
                                
                                    MsgBox "Existing Blank cell in Row/s  " _
                                    & Trim(Left(FNDBlnkRWS, Len(FNDBlnkRWS) - 2))
                                                              
                                                        
                            
                             End If
        End If
End If

End Sub

you can change:

 If Cells(i, 2).Value = NewEntry Then

to:

 If Ucase(Cells(i, 2).Value) = Ucase(NewEntry) Then

you mark resolved using the thread tools menu