Put a number on a cell and it showing word/words

[Tracyng2406]

Hi All,

Could anyone teach me how to put a number (e.g. 1) in a cell, after enter it will show a word (e.g. apple / sweet apple) ?

thanks a lot.

 

[My Aswer Is This]

You said in post 1

Could anyone teach me how to put a number (e.g. 1) in a cell, after enter it will show a word (e.g. apple / sweet apple) ?​

1. Put 1 in what cell?
2. If 1 equals "Apple" what does 2 equal?
3. Will it always be the same cell?

Where is the list of values like "Apple" "Pear"

 

[Tracyng2406]

although it's complicated, I've tried and save. it needs to debug, I've no idea where is the error

 

[Tracyng2406]

 

[My Aswer Is This]

What are you trying to do with this image?

 

[Tracyng2406]

I've tried but no success, I will give up. Anyway, thanks a lot again for everyone's time and reply.

 

[My Aswer Is This]

Sorry we were not able to help you.
But when you say I want to put a 1 in a cell and have "Apple" put into same cell we need more information.

 

[Peter_SSs]

I will give up.

I don't think that you should do that just yet.

See if you can get this to do what you want, or close to it. It again involves vba code, but has a simpler implementation than the previous suggestion.
Try these steps in a new test workbook:

1. Set up a sheet exactly like you have shown in post #5 , except for the values in B9:D21
2. Right click the sheet name tab and choose "View Code".
3. Copy and Paste the code below into the main right hand pane that opens at step 1.
4. Close the Visual Basic window & test by entering values into the B9:D21 range.
5. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, c As Range, Found As Range, SearchRows As Range
  
  Const EntryHeaderRow As Long = 8  '<- Edit if required
  
  Set Changed = Intersect(Target, Rows(EntryHeaderRow + 1 & ":" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    Set SearchRows = Rows("1:" & EntryHeaderRow - 1)
    For Each c In Changed
      If Len(c.Text) > 0 Then
        Set Found = Nothing
        Set Found = SearchRows.Find(What:=c.Text, LookAt:=xlWhole, MatchCase:=True)
        If Not Found Is Nothing Then c.Value = Found.Offset(, 1).Value
      End If
    Next c
    Application.EnableEvents = True
  End If
End Sub

Here is what my sheet looks like after following the above steps and at step 4 I have entered a "2" into cell B9, a "D" into C13 and "II" into D11.

Book1
	A	B	C	D	E	F	G	H
1	1	Apple		A	Red		I	Open
2	2	Orange		B	Blue		II	Close
3				C	Purple
4				D	Yellow
5				E	Green
6
7
8	Name	Fruit	Colour	Status
9	John	Orange
10	Ann
11	Tom			Close
12	Susan
13	Robert		Yellow
14	Jade
15	Sue
16	Helen
17	Jeffery
18	Joey
19	Dicky
20	Suki
21	Jimmy
Sheet1

If you get any errors like before, again tell us what they say (like in post #13 ) but also click the 'Debug' button on that message box and tell us what line of the code gets highlighted.

If the code works, then it may be that we still have to make some amendments if your real sheet is not set out exactly like this sample, but we will have "proof of concept".

 

[Tracyng2406]

Hi Peter,

Million thanks for your instructions. I've followed and it's work. It can help me a lot on my project!!

thanks!!!

 

[Dossfm0q]

Good Day May This will help You
But I'm Sorry about change Strings to numbers such as A to 1, B to 2 ...D to 4 ,E to 5 and I to 1 & II to 2 , maybe Format Conditions not work with Text if you can help Me

Note: Cells still have Numbers Value and Display Text

Sub AddFormatCondition()
    
    
Dim Rng As Range, MyRange(1 To 3) As Range
Dim Appear(1 To 3) As Variant, Values(1 To 3) As Variant
Dim App(1 To 3) As Variant, Val(1 To 3) As Variant
Dim RngVal As String


Set MyRange(1) = Range("B9:B31")
Set MyRange(2) = Range("C9:C31")
Set MyRange(3) = Range("D9:D31")
Appear(1) = "Apple,Orange"
Appear(2) = "Red,Blue,Purple,Yellow,Green"
Appear(3) = "Open,Close"
Val(1) = "1,2"
'Val(2) = "A,B,C,D,E" ' FormatConditions not work with Text
Val(2) = "1,2,3,4,5" ' So convert to Numbers
'Val(3) = "I,II" ' FormatConditions not work with Text
Val(3) = "1,2" ' So convert to Numbers


Range("A1:A2") = Application.Transpose(Split("2,1", ","))
Range("A1:A2") = Application.Transpose(Split("Apple,Orange", ","))


Range("D1:D2") = Application.Transpose(Split("2,1", ","))
Range("E1:E2") = Application.Transpose(Split("Red,Blue,purple,Yellow,Green", ","))


Range("G1:G2") = Application.Transpose(Split("I,II", ","))
Range("H1:H2") = Application.Transpose(Split("Open,Close", ","))


Range("A9:A21") = Application.Transpose(Split("Jone,Ann,Tom,Susan,Rebert,Jade,Sue,Helen,Jeffery,Joey,Dicky,Suki,Jimmy", ","))


Range("B9:B21") = Application.Transpose(Split("2,1,1,2,2,2,1,1,1,2,2,1,2", ","))
'Range("C9:C21") = Application.Transpose(Split("B,A,A,C,C,E,E,A,C,D,C,C,A", ","))' FormatConditions not work with Text
Range("C9:C21") = Application.Transpose(Split("2,1,1,3,3,5,5,1,3,4,3,3,1", ",")) ' So convert to Numbers
'Range("D9:D21") = Application.Transpose(Split("I,II,I,I,I,II,I,I,I,II,II,II,I", ","))' FormatConditions not work with Text
Range("D9:D21") = Application.Transpose(Split("1,2,1,1,1,2,1,1,1,2,2,2,1", ",")) ' So convert to Numbers


    For I = 1 To 3
    For Each Rng In MyRange(I)
    With Rng
    
    .FormatConditions.Delete
    App(I) = Split(Appear(I), ",")
            For c = LBound(App(I)) To UBound(App(I))
            RngVal = Split(Val(I), ",")(c)
            If IsNumeric(RngVal) = True Then
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & Rng.Address & "=" & RngVal & ""
            Else
            RngVal = """" & RngVal & """"
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & Rng.Address & "=" & RngVal & ""
            End If
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).StopIfTrue = False
            .FormatConditions(1).NumberFormat = """" & App(I)(c) & """"
            Next
    End With
    Next
    Next
    
End Sub

 

[Dossfm0q]

Good Day May This will help You
But I'm Sorry about change Strings to numbers such as A to 1, B to 2 ...D to 4 ,E to 5 and I to 1 & II to 2 , maybe Format Conditions not work with Text if you can help Me

Note: Cells still have Numbers Value and Display Text

Sub AddFormatCondition()
    
    
Dim Rng As Range, MyRange(1 To 3) As Range
Dim Appear(1 To 3) As Variant, Values(1 To 3) As Variant
Dim App(1 To 3) As Variant, Val(1 To 3) As Variant
Dim RngVal As String




Set MyRange(1) = Range("B9:B31")
Set MyRange(2) = Range("C9:C31")
Set MyRange(3) = Range("D9:D31")
Appear(1) = "Apple,Orange"
Appear(2) = "Red,Blue,Purple,Yellow,Green"
Appear(3) = "Open,Close"
Val(1) = "1,2"
'Val(2) = "A,B,C,D,E" ' FormatConditions not work with Text
Val(2) = "1,2,3,4,5" ' So convert to Numbers
'Val(3) = "I,II" ' FormatConditions not work with Text
Val(3) = "1,2" ' So convert to Numbers




Range("A1:A2") = Application.Transpose(Split("1,2", ","))
Range("B1:B2") = Application.Transpose(Split("Apple,Orange", ","))




Range("D1:D5") = Application.Transpose(Split("1,2,3,4,5", ","))
Range("E1:E5") = Application.Transpose(Split("Red,Blue,purple,Yellow,Green", ","))




Range("G1:G2") = Application.Transpose(Split("1,2", ","))
Range("H1:H2") = Application.Transpose(Split("Open,Close", ","))




Range("A9:A21") = Application.Transpose(Split("Jone,Ann,Tom,Susan,Rebert,Jade,Sue,Helen,Jeffery,Joey,Dicky,Suki,Jimmy", ","))




Range("B9:B21") = Application.Transpose(Split("2,1,1,2,2,2,1,1,1,2,2,1,2", ","))
'Range("C9:C21") = Application.Transpose(Split("B,A,A,C,C,E,E,A,C,D,C,C,A", ","))' FormatConditions not work with Text
Range("C9:C21") = Application.Transpose(Split("2,1,1,3,3,5,5,1,3,4,3,3,1", ",")) ' So convert to Numbers
'Range("D9:D21") = Application.Transpose(Split("I,II,I,I,I,II,I,I,I,II,II,II,I", ","))' FormatConditions not work with Text
Range("D9:D21") = Application.Transpose(Split("1,2,1,1,1,2,1,1,1,2,2,2,1", ",")) ' So convert to Numbers




    For I = 1 To 3
    For Each Rng In MyRange(I)
    With Rng
    
    .FormatConditions.Delete
    App(I) = Split(Appear(I), ",")
            For c = LBound(App(I)) To UBound(App(I))
            RngVal = Split(Val(I), ",")(c)
            If IsNumeric(RngVal) = True Then
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & Rng.Address & "=" & RngVal & ""
            Else
            RngVal = """" & RngVal & """"
            .FormatConditions.Add Type:=xlExpression, Formula1:="=" & Rng.Address & "=" & RngVal & ""
            End If
            .FormatConditions(.FormatConditions.Count).SetFirstPriority
            .FormatConditions(1).StopIfTrue = False
            .FormatConditions(1).NumberFormat = """" & App(I)(c) & """"
            Next
    End With
    Next
    Next
    
Range("B9").Activate' see what contain


End Sub