Change Event based on two other cell values

[carsto]

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.

[carsto]

Okay, I have something that sort of works (Half the time!) but needs fixed

Please Login or Register  to view this content.

It finds them, checks column E and messages & Exits Sub if the blank is found - WORKS GREAT *** but now I need to make it disallow the entry
If a corresponding blank cell is never found it Loops CONTINUOUSLY
-it doesn't seem to ever think the found.Address = firstaddress

I have no idea what I'm doing, so feel free to majorly critique this mess!

Once I get it working, I stilll need to make it into a Change Event, it doesn't have to happen every time a cell in Column B is changed just when the newest entry, which will be in the last row (first open) is entered.

[stevebriz]

try this and see if it does what you need.

Please Login or Register  to view this content.

[carsto]

Thank Steve, exactly what I needed

however the
If Cells(i, 2).Value = NewEntry Then
appears to be case sensitive,
is it possible to change that?

ABC123 matches ABC123
abc123 doesn't match ABC123

PS. how do you mark a post Resolved?

[stevebriz]

you can change:

Please Login or Register  to view this content.

to:

Please Login or Register  to view this content.

you mark resolved using the thread tools menu

[carsto]

Thanks!

as for Marking Resolved, the only options I have under Thread Tools is:
Show Printable Version
Email this Page
Unsuscribe from this Thread
Add a Poll to this Thread
Admin Tools - Delete Thread

[stevebriz]

ok...Yeah I can't find it now...( maybe I am confused)

[carsto]

a couple interesting problems have arisen.

thinking that on occasion one might need to correct a typo in a previously entered cell, I deleted & tried to reenter data.
If you try to enter data into any cell other than the last one in the column (first blank cell)
you get the error message, regardless of whether the data you are trying to enter is preexisting or not.
I cannot figure out why.

Also, if you try to highlight more than one cell in the column and delete the data you get a type mismatch
NewEntry = Target.Value
I presume I need to add an If that says only do all this if the target is a single cell. Maybe by counting the cells in the target range???

[stevebriz]

Try this modified version:

Please Login or Register  to view this content.

[carsto]

I knew there had to be a Target.Count-ing thing but I just couldn't find it!
My head is still buried in the world of formulas, not code!

To eliminate the error message when deleting/correcting a preceding entry (not in the LastRow), I changed this:

Please Login or Register  to view this content.

I figured the reason I was getting the error message is because the Loop searches from the LastRow up and it was not finding a duplicate, but in fact finding ITSELF.
If the alternative is to Loop from the active cell up to 1 and then also down to the LastRow, I'm not certain it's worth all that code!
I guess I could make it impossible to change an existing cell, if it's wrong, delete the row and start over. I'll have to think about this....

Thanks again for all your help Steve

[stevebriz]

In the last code I posted. I changed it to search form the last row up.( instead of the one before the last row.)..

I just then added a line to exclude including the target cell in the search.
This allows for changes in the middle of the column without and error.

The only other change that I made was to only allow the search to work if only one cells was selected.

hope this explains what I did....so you can understand.
Does it do all you want now?

[carsto]

oops, I missed that in your previous post.

Instead of copying & pasting the entire code I searched through and only entered the changed lines. Obviously missing some!

Since often the data is in series, I'm anticipating they may be tempted to paste one number into multiple cells and then go back and change each cell.
As it still allowed them to paste one piece of data into multiple cells, before
If Target.Count = 1 Then
I added:

Please Login or Register  to view this content.

again, Thank You Thank You Thank You