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

[TABLE="width: 500"]
<tbody>[TR]
[TD] I love Apples, I hate Orange, my Banana, my Grape[/TD]
[/TR]
</tbody>[/TABLE]

and on another sheet I have a ranged list:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Apple[/TD]
[TD]Red[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Yellow[/TD]
[/TR]
[TR]
[TD]Grape[/TD]
[TD]Green[/TD]
[/TR]
</tbody>[/TABLE]


I'm looking for way to get a result of

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]I love Red, I hate Orange, my Yellow, my Green[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


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)