【Excel】セル内の特定の文字だけ色を変える

Excelで大量のデータを管理している場合に、特定の文字だけ色を変えて目立たせたい時があります。

例えば、ログ情報などをEXCELで管理している際に「エラー」という文言だけを目立たせたい場合です。

↓以下のような形で色を付けたい

大量にデータが存在していた場合は、1件ずつ対応するのが非常に面倒なので、自動で対応する方法を考えてみました。

セル全体の色を変えたい場合は、置換で対応

「Excel 特定の文字 色」とかで検索すると沢山出てくる方法ですが、置換を使用すると簡単に色付をすることができます。

まず、「CTRL + H」を押下して検索と置換ウィンドウを開きます。

そして、置換後の文字列の「書式」を選択して、色を変更します。この時、置換後の文字列は検索する文字列と同じ値を設定します。

実施すると以下のようになります。セル全体の色が変わってしまいますが、これで要件を満たせる場合は最もお手軽な方法です。

ユーザーが入力した文字に特定の文字があった場合、色を変える

次に、Excelに予め入力されている文字ではなく、ユーザー入力時に文字色を変更する方法を記載します。

例えば入力を禁止したい文字列がある場合に、ユーザーにわかりやすくするために有効です。

この場合は条件付き書式を使用します。

色を変えたいセルを範囲指定している状態で、ホームタブの条件付き書式をクリックします。

新しいルールを選択します。

新しい書式ルールで「数式を使用して、書式設定するセルを決定」を選び、以下の式を入力します。

=COUNTIF(A1,"*エラー*")

※上記式のA1はセルの色を変えたい範囲の左上のセルを選択します。

そして、書式ボタンで色を選択します。すると「エラー」という文言が含まれる行が赤色に変更されます。

これは置換した場合と違い、セルの値が変更されるたびに評価されます。なので前述したとおり、ユーザーの入力値をチェックしたい場合に最適です。


セル全体ではなく文字の色だけを変える

最後に、冒頭に記載した例のようにセルの値全部ではなく、セル内の特定文字の色だけを変更したい場合について記載します。

これは、EXCELの標準機能や関数では対応が難しいためVBAを使用することにしました。

作成したコードが以下となります。

Public Sub setColor()
    '検索するキーワード
    Const KEYWORD = "エラー"
    '色を塗るセルが格納されている列
    Const COL_TARGET = 1
    
    Dim regexp As Object
    Dim i As Long
    Dim j As Long
    Dim regResult As Object
    Set regexp = CreateObject("VBScript.RegExp")
    
    With regexp
        .Pattern = KEYWORD
        .IgnoreCase = True
        .Global = True
        '1行目から使用最終行を対象にする
        For i = 1 To ActiveSheet.Cells(Rows.Count, COL_TARGET).End(xlUp).Row
            Set regResult = .Execute(ActiveSheet.Cells(i, COL_TARGET))
            For j = 0 To regResult.Count - 1
                '指定された単語があれば赤色にする
                ActiveSheet.Cells(i, COL_TARGET).Characters(Start:=regResult(j).FirstIndex + 1, Length:=Len(KEYWORD)).Font.Color = -16776961
            Next j
        Next i
    End With
    Set regResult = Nothing
    Set regexp = Nothing
End Sub

1行目の値に「エラー」という文言があれば、エラーという文言のみを赤色にしています。(CONSTで定義している部分)

正規表現でマッチさせたものをすべて赤くしているので、下記のように2回文言が出現したら、全て赤色にしています。


32 件のコメント

  • はじめまして。
    まさにやりたいことの記述が見つかり、非常に助かりました!
    ただ、記述をすべて理解できてはおりません・・・。
    For j = 0 To regResult.Count – 1
    の「-1」は何を意味していますか。
    「j=1」ではなく「j=0」から始めるのは、検索ワードが1つもないセルがありうるから、という解釈で正しいでしょうか。

    • regResultには、セル内で検索ワードがヒットした位置などが格納されるのですが
      これを取り出すときに0から始まるというルールだからです。

      最初にヒットした位置はregResult(0).FirstIndex
      2つ目にヒットした位置はregResult(1).FirstIndex
      3つ目にヒットした位置はregResult(2).FirstIndex

      こんな感じで一つずつずれます。

      • ご説明をありがとうございます!
        セル内で3つヒットした場合にregResult(2)を指定するには、3-1=2ということですね。
        -1のかわりに、-0にしてみたり、1を削除してみたりして試すと、次行でstopしてしまい、正しく書式置換できないならまだしもエラーになるために悩んでおりました。
        とても助かりました。
        ありがとうございました!

  • はじめまして。ご質問よろしいでしょうか
    検索したいキーワードなのですが
    複数のキーワードを設定することはできますでしょうか
    もし複数の単語を赤くする記述がございましたらご教授いただけますでしょうか

    例えば
    B1セルに「エラー」
    B2セルに「登録」
    B3セルに「正常」

    とあり、その複数単語を同時に染めたいのですが
    いろいろやってみても、分からず相談しました

    • 上で書いたサンプルを修正するとしたら以下のような感じですね。
      |で区切ることによって複数の単語に対応できます。

      Public Sub setColor()
      ‘検索するキーワード
      Const KEYWORD = “エラー|登録|正常”
      ‘色を塗るセルが格納されている列
      Const COL_TARGET = 2

      Dim regexp As Object
      Dim i As Long
      Dim j As Long
      Dim regResult As Object
      Set regexp = CreateObject(“VBScript.RegExp”)

      With regexp
      .Pattern = KEYWORD
      .IgnoreCase = True
      .Global = True
      ‘1行目から使用最終行を対象にする
      For i = 1 To ActiveSheet.Cells(Rows.Count, COL_TARGET).End(xlUp).Row
      Set regResult = .Execute(ActiveSheet.Cells(i, COL_TARGET))
      For j = 0 To regResult.Count – 1
      ‘指定された単語があれば赤色にする
      ActiveSheet.Cells(i, COL_TARGET).Characters(Start:=regResult(j).FirstIndex + 1, Length:=Len(KEYWORD)).Font.Color = -16776961
      Next j
      Next i
      End With
      Set regResult = Nothing
      Set regexp = Nothing
      End Sub

  • VBA(sub名:setColor)参考にさせていただきました。誠にありがとうございます。

    若干、作りをこちらで変更していますので、以下のエラーが該当しない場合は大変失礼致します。

    【実行時エラー5017 アプリケーション定義またはオブジェクト定義のエラーです。】
    キーワードに 例”(テスト)”  ”(”が全角で、”)”が半角の場合で
    19行目でエラーが発生してしまいました。
    全角と半角を逆にしても同様です。
    どちらとも全角、もしくは どちらとも半角ではエラーとなりません。

    もし、改善方法があればお教えいただければありがたいです。

    よろしくお願い致します。

  • VBAの正規表現の機能を使っているのですが、正規表現だと「半角括弧の前には円マークをつけなければいけない」ルールがあるので、 “(テスト\)”のようにします。
    あと、キーワードが1文字多くなってしまうので、Length:=Len(KEYWORD) – 1)に変えています。

    Public Sub setColor()
    ‘検索するキーワード
    Const KEYWORD = “(テスト\)”
    ‘色を塗るセルが格納されている列
    Const COL_TARGET = 1

    Dim regexp As Object
    Dim i As Long
    Dim j As Long
    Dim regResult As Object
    Set regexp = CreateObject(“VBScript.RegExp”)

    With regexp
    .Pattern = KEYWORD
    .IgnoreCase = True
    .Global = True
    ‘1行目から使用最終行を対象にする
    For i = 1 To ActiveSheet.Cells(Rows.Count, COL_TARGET).End(xlUp).Row
    Set regResult = .Execute(ActiveSheet.Cells(i, COL_TARGET))
    For j = 0 To regResult.Count – 1
    ‘指定された単語があれば赤色にする
    ActiveSheet.Cells(i, COL_TARGET).Characters(Start:=regResult(j).FirstIndex + 1, Length:=Len(KEYWORD) – 1).Font.Color = -16776961
    Next j
    Next i
    End With
    Set regResult = Nothing
    Set regexp = Nothing
    End Sub

    他にも正規表現でそのまま使えない文字があり、以下サイトが参考になりました。

    https://qiita.com/katsukii/items/1c1550f064b4686c04d4

  • ヒロユキ様

    返信と、解決のコード記載していただき、誠にありがとうございました。
    キーワードが正規表現を含むものと、含まないものの混在した複数のキーワード(配列にセットした複数ワード)での操作としている為、今回は、正規表現が含んだ”(“もしくは”)”は除いたキーワードで処理する方法に致しました。

    誠にありがとうございました。

  • ヒロユキ様
    はじめまして
    探していたものが見つかり、参考にさせていただきました
    ありがとうございます

    特定のワードを直接入力した場合や関数でそのワードのみを返した場合はしっかりと色付けされるのですが、違うワードと特定のワードを組み合わせたものを関数で返すと、何も色付けされなくなってしまいます。

    改善方法等があればお教えいただきたいと思います。
    どうぞよろしくお願いします。

  • ご返答、ありがとうございます

    そうですか、、
    リンク先の説明、とてもよく分かりました
    ご教示いただきありがとうございます!!

  • VBAを使用させていただきました。大変参考になりました。
    お示しいただいたVBAは、対象の列が指定してあります。
    私の用途では対象の行を指定して文字色を変えたいのです。そのため自力で修正を試みたのですが、初心者のためうまくいきません。
    ご教授いただけないでしょうか。

    • 記載したVBAのコードだと、COL_TARGET が何列目を対象にするかを設定しています。
      左から2列目を対象にしたいときは「Const COL_TARGET = 1」の部分を「Const COL_TARGET = 2」に変更すればいけると思います。
      もし固定した列ではなくて、毎回入力で変更したい場合は以下のような感じですかね

      Public Sub setColor()
      ‘検索するキーワード
      Const KEYWORD = “エラー”

      COL_TARGET = InputBox(“色を塗る列を指定してください”)
      If COL_TARGET = “” Then
      ‘キャンセルされた
      Exit Sub
      ElseIf IsNumeric(COL_TARGET) Then
      COL_TARGET = CInt(COL_TARGET)
      Else
      COL_TARGET = Range(COL_TARGET & “1”).column
      End If

      Dim regexp As Object
      Dim i As Long
      Dim j As Long
      Dim regResult As Object
      Set regexp = CreateObject(“VBScript.RegExp”)

      With regexp
      .Pattern = KEYWORD
      .IgnoreCase = True
      .Global = True
      ‘1行目から使用最終行を対象にする
      For i = 1 To ActiveSheet.Cells(Rows.Count, COL_TARGET).End(xlUp).row
      Set regResult = .Execute(ActiveSheet.Cells(i, COL_TARGET))
      For j = 0 To regResult.Count – 1
      ‘指定された単語があれば赤色にする
      ActiveSheet.Cells(i, COL_TARGET).Characters(Start:=regResult(j).FirstIndex + 1, Length:=Len(KEYWORD)).Font.Color = -16776961
      Next j
      Next i
      End With
      Set regResult = Nothing
      Set regexp = Nothing
      End Sub

  • ヒロユキ様、早速ありがとうございます。
    私の書き方が不十分でした。申し訳ありません。
    私の用途では、ある指定の行のみがが、色付けの対象なのです。
    そのため、1行目から使用最終行を対象にするのではなく、1「列」目から使用最終「列」を対象にする方法を求めて色々改変しております。

    • 「対象の行」と書いてくれているのに、勘違いしましたね。。。
      行指定だと以下のような感じになると思います。

      Public Sub setColor()
      ‘検索するキーワード
      Const KEYWORD = “エラー”
      ‘色を塗るセルが格納されている行
      Const ROW_TARGET = 1

      Dim regexp As Object
      Dim i As Long
      Dim j As Long
      Dim regResult As Object
      Set regexp = CreateObject(“VBScript.RegExp”)

      With regexp
      .Pattern = KEYWORD
      .IgnoreCase = True
      .Global = True
      ‘1列目から使用最終列を対象にする
      For i = 1 To ActiveSheet.Cells(ROW_TARGET, Columns.Count).End(xlToLeft).column
      Set regResult = .Execute(ActiveSheet.Cells(ROW_TARGET, i))
      For j = 0 To regResult.Count – 1
      ‘指定された単語があれば赤色にする
      ActiveSheet.Cells(ROW_TARGET, i).Characters(Start:=regResult(j).FirstIndex + 1, Length:=Len(KEYWORD)).Font.Color = -16776961
      Next j
      Next i
      End With
      Set regResult = Nothing
      Set regexp = Nothing
      End Sub

  • ありがとうございます。
    職場に行って試してみたところ、バッチリできました。
    私のものは、‘1列目から使用最終列を対象にする の下に書いているカッコ内のROW_TARGETを下のようにカッコ内の後半に書いていたためうまくいかなかったことが分かりました。
    文法の難しさを感じましたが、違いが分かると面白いですね。
    本当にありがとうございました。

    For i = 1 To ActiveSheet.Cells(Columns.Count ,ROW_TARGET).End(xlToLeft).Column
    Set regResult = .Execute(ActiveSheet.Cells(i,ROW_TARGET))
    ActiveSheet.Cells(i,ROW_TARGET).Characters(Start:=regResult(j).FirstIndex + 1,

  • VBAを使用させていただきました。 非常に参考になりました。
    また質問なのですが、このVBAは特定の文字(VBA内で指定した文字)となっていますが例えば、B列を指定してB列にあるすべての文字列と一致する、違う列にある文字の色を変えることはできるのでしょうか。
           (表現がわかりずらく申し訳ございません。)
    大変お手数ですが、ご教授いただけないでしょうか。

  • B列にキーワードを何個か書いて、それを全て処理させる感じですかね。
    それであれば以下でできるかと思います。
    コメント欄スペース使えなくしているので見づらいですが…

    Public Sub setColor()
    ‘キーワードが格納されている列(2列目)
    Const COL_KEYWORD = 2

    For i = 1 To ActiveSheet.Cells(Rows.Count, COL_KEYWORD).End(xlUp).Row
    ‘検索するキーワード
    Dim keyword As String
    keyword = ActiveSheet.Cells(i, COL_KEYWORD).Value
    Call setColorMain(keyword)
    Next i
    End Sub

    Private Sub setColorMain(keyword As String)
    ‘色を塗るセルが格納されている列
    Const COL_TARGET = 1

    Dim regexp As Object
    Dim i As Long
    Dim j As Long
    Dim regResult As Object
    Set regexp = CreateObject(“VBScript.RegExp”)

    With regexp
    .Pattern = keyword
    .IgnoreCase = True
    .Global = True
    ‘1行目から使用最終行を対象にする
    For i = 1 To ActiveSheet.Cells(Rows.Count, COL_TARGET).End(xlUp).Row
    Set regResult = .Execute(ActiveSheet.Cells(i, COL_TARGET))
    For j = 0 To regResult.Count – 1
    ‘指定された単語があれば赤色にする
    ActiveSheet.Cells(i, COL_TARGET).Characters(Start:=regResult(j).FirstIndex + 1, Length:=Len(keyword)).Font.Color = -16776961
    Next j
    Next i
    End With
    Set regResult = Nothing
    Set regexp = Nothing
    End Sub

  • ご回答ありがとうございました。
    お教えいただいた方法を会社で試したところ無事できました。
    大変助かりました!!ありがとうございました。

  • キーワードを指定するとキーワードより後ろがすべて赤色に表示されます。
    キーワードで指定した文字のみを赤くしたいのですがどうしたらいいでしょうか。
    スクリプトはコピペで行いキーワードの指定のみ変更を加えて他は変更を加えていません。

    • 自分のExcel(Office365)だと書いてあるスクリプトで指定した文字だけ赤くなっているんですよね。
      Excelのバージョンで関数の動きが違うときもあるので、それかもしれないですが、再現しないのでちょっとわかりそうにないです><

      文字数を指定しているのは以下の部分なのですが
      Lengthの値と同じ文字数を赤くしているので、Lengthの値を数値で指定すれば行けるかもしれないです。例えばキーワードが4文字なら以下のように4を指定します。

      (変更前)
      ‘指定された単語があれば赤色にする
      ActiveSheet.Cells(i, COL_TARGET).Characters(Start:=regResult(j).FirstIndex + 1, Length:=Len(KEYWORD)).Font.Color = -16776961

      (変更後)
      ‘指定された単語があれば赤色にする
      ActiveSheet.Cells(i, COL_TARGET).Characters(Start:=regResult(j).FirstIndex + 1, Length:=4).Font.Color = -16776961

  • お世話になります。
    1列目だけを変換:Const COL_TARGET = 1
    1行目だけを変換:Const ROW_TARGET = 1
    のご教授をいただいておりますが、行列全部(シート全部)のセル対象を変換する場合、
    簡単な記述方法は御座いますでしょうか?やはりFor分で回さないとダメでしょうか?
    よろしくお願いいたします。

    • 結局ループはさせてるのですが、個人的には以下のようにFor Eachを使うのがスマートな書き方がします。
      Const SHEET_TARGETで指定しているワークシートで使用しているセルをループさせています。

      Public Sub setColor()
      ‘検索するキーワード
      Const KEYWORD = “エラー”
      ‘色を塗るセルが格納されているシート名
      Const SHEET_TARGET = “Sheet1”

      Dim regexp As Object
      Dim i As Long
      Dim j As Long
      Dim regResult As Object
      Set regexp = CreateObject(“VBScript.RegExp”)

      Dim sh As Worksheet
      Set sh = ThisWorkbook.Worksheets(SHEET_TARGET)

      With regexp
      .Pattern = KEYWORD
      .IgnoreCase = True
      .Global = True
      For Each cell In sh.UsedRange
      Set regResult = .Execute(cell)
      For j = 0 To regResult.Count – 1
      ‘指定された単語があれば赤色にする
      cell.Characters(Start:=regResult(j).FirstIndex + 1, Length:=Len(KEYWORD)).Font.Color = -16776961
      Next j
      Next cell
      End With
      Set regResult = Nothing
      Set regexp = Nothing
      End Sub

  • はじめまして。
    初心者質問で申し訳無いのですが、
    ‘検索するキーワード
    Const KEYWORD = “エラー”
    この部分についてで今はエラーのみですが文字色の指定をエラーと何かと言う風に2つ以上の複数にする事は可能ですか?
    教えて頂けると幸いです。

    • 以下でどうでしょうか。例として”正常”or”エラー”を検索します。
      「Const KEYWORD = “エラー|正常”」の部分の縦線が文字の区切りになります。

      Public Sub setColor()
      ‘検索するキーワード
      Const KEYWORD = “エラー|正常”
      ‘色を塗るセルが格納されている列
      Const COL_TARGET = 1

      Dim regexp As Object
      Dim i As Long
      Dim j As Long
      Dim regResult As Object
      Set regexp = CreateObject(“VBScript.RegExp”)

      With regexp
      .Pattern = KEYWORD
      .IgnoreCase = True
      .Global = True
      ‘1行目から使用最終行を対象にする
      For i = 1 To ActiveSheet.Cells(Rows.Count, COL_TARGET).End(xlUp).Row
      Set regResult = .Execute(ActiveSheet.Cells(i, COL_TARGET))
      For j = 0 To regResult.Count – 1
      ‘指定された単語があれば赤色にする
      ActiveSheet.Cells(i, COL_TARGET).Characters(Start:=regResult(j).FirstIndex + 1, Length:=regResult(j).Length).Font.Color = -16776961
      Next j
      Next i
      End With
      Set regResult = Nothing
      Set regexp = Nothing
      End Sub

  • はじめまして、VBA非常に参考になりました。
    質問なのですが特定のセル内の特定文字の色だけを変えたいのですがその場合はどのようにしたら良いでしょうか?
    例えば、A5セルだけの特定の文字だけ色を変えたいといった感じです。
    VBA初心者なので難しくてわからなかったのでご教授の程お願いします。

    • コメント欄でもVBAソース見やすく貼る方法確立した!!

      本題ですが、以下ではどうでしょうか。
      Const TARGET = “A5″というところで対象のセルを指定しています。

      Public Sub setColor()
          '検索するキーワード
          Const KEYWORD = "エラー"
          '色を塗るセル
          Const TARGET = "A5"
      
          Dim regexp As Object
          Dim i As Long
          Dim j As Long
          Dim regResult As Object
          Set regexp = CreateObject("VBScript.RegExp")
      
          With regexp
              .Pattern = KEYWORD
              .IgnoreCase = True
              .Global = True
              Set regResult = .Execute(ActiveSheet.Range(TARGET))
              For j = 0 To regResult.Count - 1
                  '指定された単語があれば赤色にする
                  ActiveSheet.Range(TARGET).Characters(Start:=regResult(j).FirstIndex + 1, Length:=regResult(j).Length).Font.Color = -16776961
              Next j
          End With
          Set regResult = Nothing
          Set regexp = Nothing
      End Sub
      
  • はじめまして、エクセルのセルの一部の文字列の色を変える方法を探していたところこのページにたどりつきました。もしご存じだったらご教授いただきたいのですが…
    次のような表で

      A列    B列
    1 エラー  これはエラーです。
    2 正常   今日は正常です。
    3 問題   たくさんの問題があります。

    B列の各セルの中の文について、すぐ左のA列に設定したキーワードと同じ文字列だけを赤色にしたいのですが、ここで紹介されている内容を応用することで実現可能だったりしますでしょうか?

    • 列指定があると関数でなんとかしたくなるけどVBAしか思いつかないですね。。。
      VBAならこんな感じでどうでしょうか。

      Public Sub setColor()
          'キーワードがある列
          Const KEYWORD_COLUMN As String = "A"
          '色を塗る列
          Const TARGET_COLUMN As String = "B"
          '開始行 1行目からスタート
          Const START_ROW As Long = 1
          '現在のシートを対象にする
          Dim sh As Worksheet
          Set sh = ThisWorkbook.ActiveSheet
          
          Dim regexp As Object
          Dim i As Long
          Dim j As Long
          Dim regResult As Object
          Set regexp = CreateObject("VBScript.RegExp")
          
          Dim row As Long
          For row = START_ROW To sh.Cells(Rows.Count, convertToColumnNum(KEYWORD_COLUMN)).End(xlUp).row
              Dim targetCell As Range
              Set targetCell = sh.Cells(row, convertToColumnNum(TARGET_COLUMN))
              Dim keywordCell As Range
              Set keywordCell = sh.Cells(row, convertToColumnNum(KEYWORD_COLUMN))
              
              With regexp
                  .Pattern = keywordCell.Value
                  .IgnoreCase = True
                  .Global = True
                  Set regResult = .Execute(targetCell)
                  For j = 0 To regResult.Count - 1
                      '指定された単語があれば赤色にする
                      targetCell.Characters(Start:=regResult(j).FirstIndex + 1, Length:=regResult(j).Length).Font.Color = -16776961
                  Next j
              End With
          Next row
          
      
          Set regResult = Nothing
          Set regexp = Nothing
      End Sub
      
      '列のアルファベットを数値に変換する 例)A→1に変換する
      Function convertToColumnNum(ByVal address As String) As Integer
          convertToColumnNum = Columns(address).Column
      End Function
      
  • コメントを残す

    メールアドレスが公開されることはありません。

    日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)