マクロで書式ごと置換

マクロで書式ごと置換

いつもお世話になります。
今回は標記の件についてお伺いしたく質問を投稿させていただきました。

現在、[D5:CN93]に配置している文字列データを記号に置換する作業をおこなっています。置換マクロに関してはreplaceを使用し、今のところ問題なく動いています。作業工程としては、上記の範囲にあるデータの一覧を作成し、どのデータをどのような記号に置き換えたいかの一覧を附します。
例)
　[A1](置換したい記号)　　　[B1](置換したい文字列)
　　　 　○　　 　　　　　　　イチゴ
　　　 　◆　　　 　　　　　　バナナ
　
　[D5:CN93]置換対象の文字列例
　　 イチゴ,バナナ

マクロでは置換したい文字列、置換したい記号の値をreplace関数に与えて、上記の範囲にあるセル1つ1つに置換をかけていく、というものです。

今までは、文字列の書式は問題ではなかったのですが(置換後に一括変換)、上のほうから「イチゴを赤い○、バナナを黄色い◆のように、色をつけられないか」といわれました。
置換ウィザードを試してみたのですが、セル全体の文字列の色が変わってしまいうまくいきませんでした。
またreplaceで色情報も置換する方法もわかりません(無いのかもしれませんが)・・・

そこで「色情報を持たせたまま、文字列の一部を置換」する方法を教えてください。
これまでのデータを使用したいので、データは以下のように並んでいます。
　[A1](置換したい記号)　　　[B1](置換したい文字列)
　　　　赤色の○　　　　　　　　　イチゴ
　　　　黄色の◆　　　　　　　　　バナナ
　
　[D5:CN93]置換対象の文字列
　　イチゴ,バナナ

No.3 ベストアンサー 回答者： tom04 回答日時： 2010/10/19 22:03

No.1・2です！

何度もごめんなさい。

No.2のコードで不必要なものまでコピー＆ペーストしていました。
（こちらで遊んでいてそのままコードを削除せずに貼り付けてしまいました。）

変数の宣言で　M　は削除
そして、Mが絡んでいる行がありますので、その行も削除です。
M = Cells(Rows.Count, 3).End(xlUp).Row
の行が必要ありません。

どうも何度も失礼しました。m(__)m

No.2 回答者： tom04 回答日時： 2010/10/19 21:17

No.1です！

たびたびごめんなさい。
もう一度質問文を読み返してみますと
＞色情報を持たせたまま、文字列の一部を置換・・・

とありましたので前回の方法は的外れだと思います。

↓の画像のようにA・B列に記号と置換対象文字列があるとして、
データ内にB列の文字列があった場合、そのA列の記号と色に置換したい！ということでしょうか？

もしそうであればコードを↓のように訂正してみてください。

Sub test2()
Dim i, j, k As Long
For i = 5 To 93
For j = 4 To 92
For k = 1 To Cells(Rows.Count, 2).End(xlUp).Row
If Cells(i, j) Like "*" & Cells(k, 2) & "*" Then
Cells(i, j) = Replace(Cells(i, j), Cells(k, 2), _
WorksheetFunction.Index(Range("A:A"), _
WorksheetFunction.Match(Cells(k, 2), Range("B:B"), 0)))
End If
Next k
Next j
Next i
Dim L, M As Long
Dim str As String
For i = 5 To 93
For j = 4 To 92
For L = 1 To Len(Cells(i, j))
For k = 1 To Cells(Rows.Count, 1).End(xlUp).Row
M = Cells(Rows.Count, 3).End(xlUp).Row
str = Mid(Cells(i, j), L, 1)
If str = Cells(k, 1) Then
Cells(i, j).Characters(Start:=L, Length:=1).Font.ColorIndex = _
Cells(k, 1).Font.ColorIndex
End If
Next k
Next L
Next j
Next i
End Sub

尚、「黄色」は画面で判りづらいので敢えて「青」の◆にしています。
今回も前回同様別シートでマクロを試してみてください。

参考になればよいのですが、
今回も外していたらごめんなさいね。m(__)m

No.1 回答者： tom04 回答日時： 2010/10/19 18:42

こんばんは！

セル内の「イチゴ」を赤の「○」に、「バナナ」を黄色の「◆」にすれば良いわけですよね？

そういうことだとして・・・

一旦、文字列を記号に置換し、もう一度一文字ずつ舐めるように探して、○と◆の色を変えるようにしてみました。

尚、一旦マクロを実行すると元に戻せませんので
別シートにコピー＆ペーストしてマクロを実行してみてください。
一例です。

Sub test()
Dim i, j As Long
For i = 5 To 93
For j = 4 To 92
If Cells(i, j) Like "*イチゴ*" Then
Cells(i, j) = Replace(Cells(i, j), "イチゴ", "○")
ElseIf Cells(i, j) Like "*バナナ*" Then
Cells(i, j) = Replace(Cells(i, j), "バナナ", "◆")
End If
Next j
Next i
Dim k As Long
Dim str As String
For i = 5 To 93
For j = 4 To 92
For k = 1 To Len(Cells(i, j))
str = Mid(Cells(i, j), k, 1)
If str = "○" Then
Cells(i, j).Characters(Start:=k, Length:=1).Font.ColorIndex = 3
ElseIf str = "◆" Then
Cells(i, j).Characters(Start:=k, Length:=1).Font.ColorIndex = 6
End If
Next k
Next j
Next i
End Sub

尚、前半部分の文字列の置換は大丈夫のようなので、後半部分の色変更だけ別のマクロをしてもOKだと思います。

参考になればよいのですが・・・m(__)m