help with macros

[fooldiddy]

how would i go about creating a macro that would cause certain text, that when inputted into cells, will change to other text?

i.e. joe shmo types his name "joe shmo" but in the cell it reads "poop"

 

[Gates Is Antichrist]

That would certainly weird out the user, wouldn't it?

This code on the appropriate worksheet(s) would accomplish that, assuming they entered it within a range named foo

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
  If Intersect(Target, Range("foo")) Is Nothing Then Exit Sub
  If Target.Value = "joe shmo" Then Target.Value = "poop"
End Sub

 

[fooldiddy]

so for the range.....instead of "foo" I just enter what columns or cells i want to be affected?

 

[Gates Is Antichrist]

Sorry for just noticing your reply. The answer is "yes."

 

[Scott Huish]

Why in the world would you want it to say that?

But anyway, You'll want to add a bit to that:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range, d As Range
Set d = Intersect(Range("A1:A10"), Target)
If d Is Nothing Then Exit Sub
Application.EnableEvents = False
    For Each c In d
        If c = "joe shmo" Then c = "poop"
    Next
Application.EnableEvents = True
End Sub

 

[Gates Is Antichrist]

Thanks for the commentary and expanding the code beyond the requested scope. Nice code.

O.P., I apologize for not flipping off events as astutely demonstrated by hotpepper. While it doesn't change the outcome (well, speeds it up a few milliseconds), it certainly does belong there. Thank you, hotpepper.

 

[fooldiddy]

its not working for some reason.

Maybe i should add that I haven't ever worked with macros before.

I entered the code into the macros; went back to the sheet and tried the characters. Nothing?

I changed a snippet in the code because i want the "1" s in certain columns to show up as "poop"

 

[Scott Huish]

It needs to go into the worksheet module for the sheet where you want this to happen on.

Copy the code.
Right click the sheet tab for the sheet where you want this to happen on.
Click on View Code.
Paste into white area.
Hit Alt-q

 

[fooldiddy]

how could i do this for multiple cells and columns. like a universal code that will affect the entire spreadsheet