Mass SUBSTITUTE using a list?

[ArodSnaux]

Is it possible to use SUBSTITUTE (or some other method) using a list of inputs?

For example, my target cell contains the string

I love Apples, I hate Orange, my Banana, my Grape

<tbody>
</tbody>

and on another sheet I have a ranged list:

Apple	Red
Orange	Orange
Banana	Yellow
Grape	Green

<tbody>
</tbody>

I'm looking for way to get a result of

I love Red, I hate Orange, my Yellow, my Green <tbody> </tbody>

<tbody>
</tbody>

Obviously I could nest SUBSTITUTE, by my real list contains over 200 entries!

I tried this: =SUBSTITUTE(GF39,NAMES!A:A,NAMES!C:C) but to no avail.

Help? I'm programmer so I can always fall back to VBA, but I figured you guys might know a built-in way to do this.

 

[ArodSnaux]

Okay I broke down and learned enough VBA to do it:

Function SUBSTITUTEMANY(myStr As String, lookupRange As Range)    


    Dim cell As Range
    Dim col As Range
    
    Set col = lookupRange.Columns(1).Cells
    
    For Each cell In col
        newStr = Application.WorksheetFunction.VLookup(cell.Value, lookupRange, 2, False)
        Debug.Print newStr
        myStr = Application.Substitute(myStr, cell.Value, newStr)
    Next cell


    SUBSTITUTEMANY = myStr


End Function

After that its just a matter of typing something like this in a cell:

=SUBSTITUTEMANY(A1,[MyTable.xlsm]Sheet1!$A$1:$B$24)