To change cell value automatically

Solved/Closed
Dhanuj - Feb 14, 2016 at 04:51 AM
 Dhanuj - Feb 22, 2016 at 10:08 PM
Hello everyone here, this is my first question in this forum. In Excel 2013, If I type 1 in cell A1, then it should automatically change as Apple in same A1. Likewise 2 in A2 should automatically change as Orange in same A2. Kindly provide me a solution. Thanks in advance.


4 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 15, 2016 at 11:43 AM
Hi Dhanuj,

Welcome to this forum!

Use the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:A2")) Is Nothing Then Exit Sub
If Range("A1").Value = 1 Then Range("A1").Value = "Apple"
If Range("A2").Value = 2 Then Range("A2").Value = "Orange"
End Sub


To use this code:
- Hit Alt+F11 to open a new window.
- On the left side, find Sheet1 and double click it.
- Paste the code in the big white field.
- Close the (newly) opened window.

Best regards,
Trowa
Hi TrowaD,
Thanks for your answer. It worked. Cell A1 is selected and Value 1 is entered and it changes to "Apple" automatically as per your previous answer. Now I need it as a extended option. If we enter Value 1 anywhere in Column A, it should change as Apple in the respective cell in which we are entering that Value. Thanks in advance.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 16, 2016 at 11:28 AM
Hi Dhanuj,

Just to be clear:
Enter "1" in column A --> automatic change to "Apple".
Enter "2" in A2 --> automatic change to "Orange".

Is this what you are after?

Best regards,
Trowa
Hi TrowaD,
Yes that's what I have asked earlier and you have solved my issue and thanks for that. Now this is an another question.
Entering value 1 in any row of A column should change as Apple in the respective cell and Value 2 in any row of A column should change as Orange in the respective cell. Kindly help me on this regard.
Thanks in advance.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 18, 2016 at 10:56 AM
Hi Dhanuj,

Ok, for that request use the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
If Target.Value = 1 Then Target.Value = "Apple"
If Target.Value = 2 Then Target.Value = "Orange"
End Sub


Best regards,
Trowa

Hi TrowaD,

Thank you so much for giving me a solution.
Hi TrowaD,
This is my last question about this topic. What is the macro to use same kind of function in Column B as well?
Column A should change as
1 = Apple
2 = Orange
Column B should change as
1 = Banana
2 = Grapes
Earlier you provided me the solution for Column A and it works good. Thanks for that. With this answer, my query will be solved about this topic.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Feb 22, 2016 at 11:59 AM
Hi Dhanuj,

And for that request use the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A:A")) Is Nothing Then
    If Target.Value = 1 Then Target.Value = "Apple"
    If Target.Value = 2 Then Target.Value = "Orange"
ElseIf Not Intersect(Target, Columns("B:B")) Is Nothing Then
    If Target.Value = 1 Then Target.Value = "Banana"
    If Target.Value = 2 Then Target.Value = "Grapes"
End If
End Sub


Does this solve this topic ... ? ;)

Best regards,
Trowa
Hi TrowaD,

You have cleared my query. Thank you so much.