40歳からExcelに挑戦!

Excel VBA セルごとではなく、特定の単語にだけ色をつける!続編


こんばんは。雪ん子です。
お越しいただきありがとうございます。

このブログはExcelやAccessの備忘録です。

初心者なので、変なコードとか出てくるかもしれません( ノД`)
もっと良い方法があったら、どしどしコメント下さいね。

f:id:snow0303:20190311203407j:plain


 

特定の単語だけに色をつける文字の大きさや、セルの背景なども変更可能!)

 
以前の記事にご質問をいただき、背景色も同時に設定出来るように改修しました!
 
もちろん以前と同様に、セル内に複数の単語が入っていても大丈夫です!
 
Excel VBA セルごとではなく、特定の単語にだけ色をつける!
 
セルに色を付けるのは条件付き書式でできるのですが、設定も面倒ですしね。
VBAでやってしまった方が楽ですもんね。
 
質問いただいたおかげで、勉強になりました。
ありがとうございます。
 



私の設定

①色付けしたい表

A B
1 おやつ 夜ご飯
2 りんご、バナナ 冷やし中華、コーラ
3 みかんとヨーグルト 牛丼、ウーロン茶
4 バナナ 親子丼とほうじ茶
5 りんご 牛丼

 
 
②「リスト」と名前をつけたシートを用意

「リスト」シートの内容

  • A1からC5に目立たせたいキーワードを入力(空欄があっても大丈夫)
  • 文字はマクロ実行後に付けたい色にしておく
  • 文字サイズも設定
  • セルの色も設定(下の表では文字の下だけ色が付けていますが、実際はセルを色付けしています。)
A B C
1 りんご 冷やし中華 コーラ
2 みかん 親子丼 緑茶
3 イチゴ 牛丼 麦茶
4 スイカ ウーロン茶
5 ほうじ茶

 

VBA

Sub Macro1()
  
For Each 指定キーワード In Worksheets("リスト").Range("A1:C5") 'リストシートのA1からC5の範囲内のすべてのキーワードで実行

    If 指定キーワード <> "" Then                '空文字でない場合だけ実行
      背景 = 指定キーワード.Interior.Color    'セル背景色を読み込む= 指定キーワード.Font.ColorIndex     '色を読み込む
      文字サイズ = 指定キーワード.Font.Size   '文字サイズを読み込む
      検索文字長 = Len(指定キーワード)        'キーワード長を調べる
      
      For Each セル In Range("A2:B5")         '色を変更したい範囲を設定
      
        検索開始位置 = 1                    '「検索開始位置」変数を初期化
        
        Do                                  '検索文字を発見した位置を繰り返し調べる
          発見位置 = InStr(検索開始位置, セル.Text, 指定キーワード)
          If 発見位置 = 0 Then Exit Do    '発見できなかったらループから脱出
          With セル   '発見できたセルで文字の色とサイズを設定
            .Interior.Color = 背景  '同じセル背景色にする
            .Characters(発見位置, 検索文字長).Font.ColorIndex = '同じ色にする
            .Characters(発見位置, 検索文字長).Font.Size = 文字サイズ '同じ文字サイズにする
            .Characters(発見位置, 検索文字長).Font.Bold = True '太字にする
            .Characters(発見位置, 検索文字長).Font.Italic = True '斜体にする
          End With
            検索開始位置 = 発見位置 + 検索文字長    '「検索開始位置」を設定
        Loop
      Next
    End If
  Next

End Sub


前回と違うのは、
最初の方で、
背景 = 指定キーワード.Interior.Color 'セル背景色を読み込む
真ん中あたりで、
.Interior.Color = 背景 '同じセル背景色にする
が加わっているところです。

※シート名や範囲は各自変更してください。

※「For Each セル In Range("A2:B5") '色を変更したい範囲を設定」
が単語に色を付けたい範囲です。

※目立たせるためのキーワードを同じシート内に設定する場合は、
「For Each 指定キーワード In Worksheets("リスト").Range("A1:C5") 'リストシートのA1からC5の範囲内のすべてのキーワードで実行」
からシート名を指定する部分「Worksheets("リスト").」を省く。

「For Each 指定キーワード In Range("A1:C5") 'A1からC5の範囲内のすべてのキーワードで実行」

もちろんセルの場所も好きな場所に変更して大丈夫です。

※色、文字サイズ、太字、斜体は必要なければ削除してください。
例)斜体は必要なし
「.Characters(発見位置, 検索文字長).Font.Italic = True '斜体にする」
を省く。

マクロ実行後

A B
1 おやつ 夜ご飯
2 りんご、バナナ 冷やし中華、コーラ
3 みかんとヨーグルト 牛丼ウーロン茶
4 バナナ 親子丼とほうじ茶
5 りんご 牛丼

 
一つのセルに「リスト」シートの表に含まれる文字が2種類あり、
どちらもセルの色が設定されている場合、
A1の設定→B1の設定→C1の設定→A2の設定→B2の設定→C2の設定・・・の順で、
あとから出てくるセルの色が有効になります。たぶん……。
例)りんごとほうじ茶の両方が含まれるセルだと、ほうじ茶のセルの色になります。