Report

To change cell value automatically [Solved]

Ask a question Dhanuj - Latest answer on Feb 22, 2016 10:08PM
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.
See more 
Helpful
+0
moins plus
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
Dhanuj- Feb 16, 2016 04:19AM
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.
Reply
TrowaD 1989Posts Sunday September 12, 2010Registration date ModeratorStatus September 27, 2016 Last seen - Feb 16, 2016 11:28AM
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
Reply
Dhanuj- Feb 16, 2016 08:45PM
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.
Reply
Add comment
Helpful
+0
moins plus
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

Dhanuj- Feb 18, 2016 10:25PM
Hi TrowaD,

Thank you so much for giving me a solution.
Reply
Add comment
Helpful
+0
moins plus
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.
Add comment
Helpful
+0
moins plus
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
Dhanuj- Feb 22, 2016 10:08PM
Hi TrowaD,

You have cleared my query. Thank you so much.
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!