Validate Cell Content AfterUpdate

[Guest]

I know that the title is hardly clear but I will hope to explain what I mean.
I hope someone can help me, you have been wonderful in the past.

I want to be able to enter a value in a cell and then have it validate that
value and change the content of the cell to the appropriate text. Example:

In cell A1, I enter an "x" and when I hit enter, it changes the text to say
"Removed" or if I enter "y" or leave it blank and hit enter, it changes it to
say "N/A". This is like validation on the go type of thing. Can I write a
built-in formula and have it applied to a range of cells which will then
react the way I want when I enter data into them later?

I hope this is clear, its crystal in my head but not when it comes out in
words. Amazing how that works.

 

[Peter T]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sNew As String

If Not Intersect(Target, Range("A1")) Is Nothing Then
On Error GoTo ErrorExit
Select Case UCase(Range("A1").Text)
Case "Y", "": sNew = "N/A"
Case "X": sNew = "Removed"
End Select

If Len(sNew) Then
Application.EnableEvents = False
Range("A1") = sNew
End If
End If

ErrorExit:
Application.EnableEvents = True
End Sub

This "event" code belongs in the relavent sheet module.
Right-click sheet tab > View code and paste
When all working OK close the module windows before saving

Regards,
Peter T

 

[Guest]

Peter,

First of all, thank you so very much for taking the time to help me out.

However, I don't know if I am doing something wrong, although this is pretty
straight forward and I think I am doing it right but absolutely nothing
happens.

Nothing changes, no error even, anything I need to do that I am not doing?

Your assistance is greatly appreciated...

ps. would posting the actual setup of the sheet help you more?

 

[Guest]

Peter,

Sorry, but after I wrote the reply I did some debugging and tinckering and
got it to work actually.

Thank you very much for your help,
I owe you one.

Michael