Event Macro to insert text in cell based on input of another cell

Hi,

Background:
I have a macro that is used by the end user to validate if the code they input in a form will be accepted in our system or not. They input the coding in a specific range of cells (say B20:E40) and press the macro which returns a message in the column N which says whether the code was successful or invalid. So if the coding in row 20 is invalid the macro returns a message in N20 that says invalid.

The problem with this structure is when the coding is changed or deleted from the row, the message in column N remains the same until they re-run the macro.

Event Macro:
I want to create an event macro to be triggered in the event that any cell in range(B20:E40) is changed to identify that the code needs to be re-validated. I am able to create this using this simple macro:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("B20:E40"), Target) Is Nothing Then
Range("N" & Target.Row) = "Validation Required"
End If

End Sub

Here's my problem and sorry it took so long to get to the point, if the code in the row is erased I want the message in column N to disappear.

Outcome:
So if the coding is changed in a row, column N in that row = "Validation Required", if no code in the row, column N = "". Then once the validation macro is ran column N is replaced with "success"or "invalid". From there any change or deletion of rows will results in one of the two outcomes created by the event macro.

I hope that makes sense, let me know if you need more information.

Thanks,

Nick


Note: Solved in Cross-post

http://vbaexpress.com/forum/showthread.php?t=43561

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

crosspost:

http://vbaexpress.com/forum/showthread.php?t=43561

Thanks snb.

Nplouffe,

I'm afraid your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).