To change cell value automatically [Solved/Closed]

Report
-
 Dhanuj -
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 replies

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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.
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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.
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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.
Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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.