Try this:

Sub AdvancedReplaceMacro()
folderspec = "c:\test"
Dim fs, f, f1, fc
Dim strReplacementText As String
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.Files
For Each f1 In fc 'runs through all files
Workbooks.Open f1
For intCount = 1 To Workbooks(f1.Name).Worksheets.Count 'runs
through all sheets
Workbooks(f1.Name).Worksheets(intCount).Select
Cells(1, 1).Select 'selects cell A1 - you may need to
change this
Do
Select Case ActiveCell.Value 'changes values
Case "apple 1", "apple 2", "apple 32"
strReplacementText = "orange1"
Case "apple 8", "pineapple21", "pineapple5",
"pineapple3", "pineapple43"
strReplacementText = "orange22"
Case Else
MsgBox "Cannot find '" & ActiveCell.Value & "'.",
vbInformation
strReplacementText = ""
End Select
If strReplacementText < "" Then Call
UpdateValue(strReplacementText)

strReplacementText = ""
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell.Row =
Cells.SpecialCells(xlCellTypeLastCell).Row 'loop until end
Next intCount
Next
End Sub
Function UpdateValue(strReplacementText As String)
'updates colour and comment
Selection.Interior.ColorIndex = 36
Range(ActiveCell.Address).AddComment
Range(ActiveCell.Address).Comment.Visible = False
Range(ActiveCell.Address).Comment.Text Text:="Old Value:" & Chr(10)
& ActiveCell.Value
Range(ActiveCell.Address).Value = strReplacementText
End Function


markx wrote:
Hello guys,



I have 14 files (every of them with several tabs) where there are several
replacements to do.

55 old values need to be replaced with 12 new strings, f. ex:



old value_ to be replaced with new value_

apple 1 orange1

apple 2 orange1

apple 32 orange1

apple 8 orange22

pineapple21 orange22

pineapple5 orange22

pineapple3 orange22

pineapple43 orange22

grape1 orange444

grape122 orange444

. .



Could you help me to write a macro that will :

1) treat all the files/tabs in the specified folder (f. ex. C:\test) -
I'll put there all the files that I want to change

2) make changes to them (all files, all tabs) based on the open file
"replacement_template.xls" (where I have column A (old values) and B (new
values))

3) highlight (in yellow) all the changed cells and put in comments to
the every changed cell the old value



Seems at the same time easy and complex to do.

In advance many thanks for any hints/draft solutions you could provide!

Regards,

Mark