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]

Hey,

How about a worksheet change event:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        If UCase(Target.Value) = "APPLES" Then
            Target.Value = "Fruits"
        End If
    End If
End Sub

 

[MadBern]

Hmmm, I might be messing it up, but I can't get that to work.

 

[tyija1995]

Are you pasting it into the sheet code or a module? If you have pasted in to a module it will fail, when you open up VBA under the project with the workbook you are focusing on, double click the sheet in question (e.g. Sheet1) and paste in there

 

[MadBern]

Yes, I did. But still no go, I'm afraid.

 

[Repush]

please explain "no go"

 

[tyija1995]

Strange... so if you type apples in to cell A1 on the sheet you pasted the worksheet change event code it doesn't update to fruits?

 

[MadBern]

It means it doesn't work.
Nothing happens when I try typing "APPLES" into A1.

 

[tyija1995]

What excel version are you using?
I'm on O365 and it works fine for me.

 

[MadBern]

Same, MS O365 ProPlus