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 552
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
0
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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

0
Hi TrowaD,

Thank you so much for giving me a solution.
0
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
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
0
Hi TrowaD,

You have cleared my query. Thank you so much.
0