Replace one text with another

[MadBern]

Hey folks,

Is this possible?
I want a particular word to be replaced by a different word in the same cell as it's written in.

Example: If someone in cell A1 types "Apples" I want the text automatically change to "Fruits".

I've tried Conditional formatting and Data validation without success.
I get to respond correctly if I type in a number, f.ex. "1" and it changes to "Fruits", but not if I type in any kind of text.

 

[tyija1995]

OK, in a module try this short code:

Sub enableEvents()
    Application.enableEvents = True
End Sub

Run that then re-try the apples in cell A1.

 

[Repush]

does the macro run? you can check this by placing a breakpoint

 

[MadBern]

That worked, thanks a bunch!
Now I only have to make it do that for every cell in a spesific range.

 

[tyija1995]

That worked, thanks a bunch!
Now I only have to make it do that for every cell in a spesific range.

What range do you want it applied to ? and is it JUST for the word "apples"?

 

[Twollaston]

Hey folks,

Is this possible?
I want a particular word to be replaced by a different word in the same cell as it's written in.

Example: If someone in cell A1 types "Apples" I want the text automatically change to "Fruits".

I've tried Conditional formatting and Data validation without success.
I get to respond correctly if I type in a number, f.ex. "1" and it changes to "Fruits", but not if I type in any kind of text.

This can be done for all instances of Apples

Choose File -> Options -> Proofing -> Autocorrect options

In the "replace" field type Apples, in the "with" field type fruits

This will automatically change it from apples to fruits when entered

 

[tyija1995]

This can be done for all instances of Apples

Choose File -> Options -> Proofing -> Autocorrect options

In the "replace" field type Apples, in the "with" field type fruits

This will automatically change it from apples to fruits when entered

Whilst that is an alternative it is case-sensitive and will change every newly entered cell where "Apples" is inputted, to "fruits", which may not be desired!

EDIT: It appears case-sensitive for me at least, as it works on "Apples" but not "apples" (?)

 

[Twollaston]

Whilst that is an alternative it is case-sensitive and will change every newly entered cell where "Apples" is inputted, to "fruits", which may not be desired!

EDIT: It appears case-sensitive for me at least, as it works on "Apples" but not "apples" (?)

Yes, true it is certainly not ideal

 

[Twollaston]

EDIT: It appears case-sensitive for me at least, as it works on "Apples" but not "apples" (?)

Strangely, mine is not case sensitive in excel 2010

 

[MadBern]

What range do you want it applied to ? and is it JUST for the word "apples"?

Range: A1:F30
And yes, it should apply only to that one word.

 

[tyija1995]

Range: A1:F30
And yes, it should apply only to that one word.

OK for a range A1:F30 try the following code: (note i am using phone atm so can't actually test it! If it does not work and you don't have a solution already i can type one up at home when I get to my PC)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Dim iSect As Variant
Set rng = Range("A1:F30") 
Set iSect = Application.Intersect(rng, Target) 
    If iSect Is Nothing Then
        'do nothing
    Else
        If UCase(Target.Value) = "APPLES" Then
            Target.Value = "Fruits"
        End If
    End If
End