• ベストアンサー
  • 困ってます

表にリストの文字列を含むセルに着色したい

いつも大変お世話になっております。 添付図のような文字列がちりばめられた表から、範囲を指定してリストに入力した文字列を含むセルにリストの色で着色したい。 これを今まで手作業してましたが、ソロソロ限界ですので何とかならないかと。。。 例えば、A列からD列の表で、B5:C10の範囲を指定して、別に枠取りしたリストに文字列を入力すると、リストの色を前方一致で、図の場合、B7とC8が緑、B9は青に・・というように着色したいのです。 表の大きさやリストの対象文字列の数はシートによってマチマチです。 検索対象範囲、リストの範囲はコード中で指定出来れば汎用性がでるのでありがたいです。

共感・応援の気持ちを伝えよう!

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.8

画像の見る限り「リスト」という文字を探して、 左を検索対象範囲、下をリストの範囲にすれば、 いちいち範囲指定をする必要が無く、シートを開いて実行するだけでいいのではないか。 必ず「リスト」の文字がある前提です。 Option Explicit ' Sub Macro1()   Dim StartAddress As String   Dim List As Range   Dim Find As Range   Dim Area As Range '   Set Find = Cells.Find("リスト", LookAt:=xlWhole).Offset(1)   Set List = Cells(Rows.Count, Find.Column).End(xlUp)   Set Area = [A:A].Resize(, Find.Column - 1)   Area.Interior.Pattern = xlNone   Application.ScreenUpdating = False '   For Each List In Range(Find, List)     Set Find = Area.Find(List, LookAt:=xlPart) '     If List > "" And Not Find Is Nothing Then       StartAddress = Find.Address '       Do         Find.Interior.Color = List.Interior.Color         Set Find = Area.FindNext(Find)       Loop Until Find.Address = StartAddress     End If   Next List End Sub

共感・感謝の気持ちを伝えよう!

質問者からのお礼

これは(当方の)想像を絶する究極のプロシージャーです! シートに「着色」の実行ボタンを配置して、諸々一発解決できました。

質問者からの補足

BSにして早々に閉め切ろうと思い、他のご回答者にお礼を書きながら、こんな回答が得られるならもう少しシートのレイアウトを考えて例示すればよかった!!と「後知恵」・・・ で、やっぱり、もし可能なら非常に厚かましいのですが 1.リストを表の左側(AとかB列) に改良してもらえないでしょうか? 表が結構大きく、列数もマチマチなので、A列(やB列)にリストがあった方が非常に使い易いので。

その他の回答 (11)

  • 回答No.12

imogasiの言うとおり、私のプログラムは、入力直後に色が変わらす、変更があればその都度プログラムを実行する必要があります。 質問には、変更後すぐに色が変わると書いて無かったので、それでいいと判断しました。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ハイ、むしろ実行ボタンで実行する方が使い易いですし、そのつもりで質問しました。 (言い訳になりますが、自作出来ないレベルなので細部に気が付かない) 実際の使用方法は目的とする文字列をリストに入力し、検索実行です。 複数回に分けて実行することもありますので一々色が変わったらかえって煩わしいと思いますので。

  • 回答No.11

リストを表の左側という事は、 F列にリストがあれば、G列以右が対象ですか?   Set Area = Columns(List.Column + 1).Resize(, Columns.Count - List.Column) に直して下さい。 私の様に、昼間回答しない人もいるので、1日待った方がいいです。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ハイ、ご回答が夜だったので一晩は待つつもりでした。 無事期待通りに動くことを確認しました。 最初の回答で即〆切のつもりでしたが段々欲が出てきて・・・ これでリストが対象表の左右どちらでも使えるようになりました。 シートによっては左に列を挿入すると既のにVBAに影響する可能性のあるシートもありますので、列を挿入しないことを前提で表の右側をイメージして質問した次第です。 あの添付図からのご推察に感心しました。 範囲は都度指定するもの、との感覚でした。 今後の同様の質問時に別の意味でも有用なご回答で、併せて御礼申し上げます。

  • 回答No.10
  • imogasi
  • ベストアンサー率27% (4675/16766)

ある回答にべたぼれのようだが、小生が条件付き書式を使った VBAのコードに拘わったのは、元のデータを修正したり、変わった時 即座に正しく反応してくれるためだ。 Find法では、もう一度プログラムを実行しないとならないはず。 (実験済み) この再実行をしなくても反応してくれるコードも書けると思うが 難しいように思う。 === ただ本質問の例に限れば、検索範囲のデータが、プログラム実行後に 変わることはないように思うのだが、一般には、即座に正しく 反応してくれるのはありがたいと思って、いつも、気にかけている。 ーー 自分がVBAコードの相当な判定者のように思っているようだが、質問者はそこまで考えたたか?

共感・感謝の気持ちを伝えよう!

質問者からのお礼

検証までしていただいて、何度ものご指摘ありがとうございます。 実際には自動実行である必要は全くなく、リストに時々の文字列を入力し >シートに「着色」のVBA実行ボタンを配置して・・・ が当方には使いやすいのでそのようにして使う予定です。 また >自分がVBAコードの相当な判定者のように思っているようだが、質問者はそこまで考えたたか? 全く思っていませんし、そこまで考えていません。 タダご回答の中で、素人の当方にとっては非常に使いやすいVBAだと思っただけです。

  • 回答No.9
  • kkkkkm
  • ベストアンサー率62% (1287/2056)

> No8さんのご回答が非常に使いやすく当方にとって汎用性が高いのでこちらをBSにさせて頂きますのでご了承ください。 はい、どの回答を採用しても自由なので気にしないでください。 ただ、「前方一致」と「部分一致」の違いは理解して質問してくださいね。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ハイ!アドバイスありがとうございます。 今後は気を付けたいと思います。 実際の表では前方一致なのですが、前方一致は部分一致に含まれる、*を付ける場所を変えれば良いだけ、という認識でした。 やりたいことの内容の把握(当方の知識レベル)不足しているということだと思います。 長い目で見てやってください。(老い先短いですが・・) kkkkmさんには過去にもお世話になったことが有ります。 これに懲りずに機会があれば今後とも宜しくお願いします。

  • 回答No.7
  • kkkkkm
  • ベストアンサー率62% (1287/2056)

No.3No.4のおまけです。 先日、他の質問で「セルに色を付けたい」の後に、「文字に色を付けたい」に変更とかがあったのでおまけ追記しておきます。 そのような場合は S.Interior.Color = F.Interior.Color を すべて文字の色の場合 S.Font.Color = F.Interior.Color 検索した文字だけ色付けの場合 S.Characters(1, Len(F.Value)).Font.Color = F.Interior.Color に変更するだけで。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

何度ものご丁寧なご回答に感謝!感謝!! 最初のご回答で何とか出来そうだったのですが、VBAに慣れていない途方にとって#No8さんのご回答が非常に使いやすく当方にとって汎用性が高いのでこちらをBSにさせて頂きますのでご了承ください。 当方の実力を考慮した補足回答を含め、本当にありがとうございました。

  • 回答No.6
  • imogasi
  • ベストアンサー率27% (4675/16766)

#2です。 VBAの経験があるかどうか、の補足も出ないうちに、どんどんVBAの回答が出ているが。 「前方一致」とは、セルの文字列の先頭に現れていると解して、調べて、やってみると,結構細部で難しかった。 「大きい」という語句(セルの文字列の、先頭部に来る条件で)でやって見た。 標準モジュールに 例 データ A1:A10に 大きい川 口が大きい 大きな湖のほとり 大きい顔をして 湖の大きい波 大きい口を開ける 大きな湖 大きい川 被害が大きい 大きくなれば と入れて下記を実行した。 === Sub Test01() Range("a1:A10").Select Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=FIND(""大きい"",A1)=1" 'Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority With Selection.FormatConditions(1).Interior .PatternColorIndex = xlAutomatic .Color = 49407 .TintAndShade = 0 End With 'Selection.FormatConditions(1).StopIfTrue = False End Sub ーー 条件は先頭の「大きい」という語句。 結果は、 A列 1,4,6,8行に、セルの塗りつぶしの色が着いた。 ーー もし使えるなら、対象セル範囲と語句を修正してやってみたら。 ただし、上記ではA2,AB、DXのそれぞれは、OR条件で式を組み立てられるかどうか やって見てない。最悪では、語句ごとに繰り返しコードを作らないといけないかも。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご指摘ありがとうございます。 #No8さんのご回答で当方の想像以上の解決策が得られました。

  • 回答No.5
  • kkkkkm
  • ベストアンサー率62% (1287/2056)

No.3No.4の補足です。 一致判定の部分は InStr(1, S.Value, F.Value) = 1 でなくても、Likeを使って S.Value Like F.Value & "*" でもいけます。 最近InStr使う事が多かったので反射的にInStr使いました。

共感・感謝の気持ちを伝えよう!

  • 回答No.4
  • kkkkkm
  • ベストアンサー率62% (1287/2056)

No.3の追加です。 > リストに文字列を入力すると というのが、入力したときに実行するという意味でしたら リストの入力範囲がF2:F6として その入力されたセルのみの文字列で検索 Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("F2:F6")) Is Nothing Or Target.Value = "" Then Exit Sub End If Dim S As Range, mRng As Range Application.ScreenUpdating = False Set mRng = Range("B5:C10") For Each S In mRng If InStr(1, S.Value, Target.Value) = 1 And Target.Value <> "" Then S.Interior.Color = Target.Interior.Color End If Next Application.ScreenUpdating = True End Sub 上記を実行(最初に消さない場合)していれば意味のない動作だと思いますが 入力したセル以外も含めてF2:F6にある文字列を検索 Private Sub Worksheet_Change(ByVal Target As Range) If Application.Intersect(Target, Range("F2:F6")) Is Nothing Or Target.Value = "" Then Exit Sub End If Dim S As Range, F As Range, mRng As Range, LRng As Range Application.ScreenUpdating = False Set mRng = Range("B5:C10") Set LRng = Range(Cells(2, "F"), Cells(Rows.Count, "F").End(xlUp)) For Each S In mRng For Each F In LRng If InStr(1, S.Value, F.Value) = 1 And F.Value <> "" Then S.Interior.Color = F.Interior.Color Exit For End If Next Next Application.ScreenUpdating = True End Sub なお、最初に選択したセルの色を消したい場合は以下のコードを最初に入れてください。 mRng.Interior.ColorIndex = xlNone とかです。

共感・感謝の気持ちを伝えよう!

  • 回答No.3
  • kkkkkm
  • ベストアンサー率62% (1287/2056)

> 検索対象範囲、リストの範囲はコード中で指定出来れば汎用性がでるのでありがたいです。 こちらか(都度コードを変更する) Sub Test() Dim S As Range, F As Range, mRng As Range, LRng As Range Application.ScreenUpdating = False Set mRng = Range("B5:C9") Set LRng = Range(Cells(2, "F"), Cells(Rows.Count, "F").End(xlUp)) For Each S In mRng For Each F In LRng If InStr(1, S.Value, F.Value) = 1 And F.Value <> "" Then S.Interior.Color = F.Interior.Color Exit For End If Next Next Application.ScreenUpdating = True End Sub こちらか(利用者に範囲を選択させる) Sub Test2() Dim S As Range, F As Range, mRng As Range, LRng As Range On Error Resume Next Set mRng = Application.InputBox(Prompt:="検索対象範囲を選択してください。", Type:=8) Set LRng = Application.InputBox(Prompt:="リストの範囲を選択してください。", Type:=8) On Error GoTo 0 If mRng Is Nothing Or LRng Is Nothing Then MsgBox "範囲選択が間違ってるかキャンセルされました", vbInformation Exit Sub End If Application.ScreenUpdating = False For Each S In mRng For Each F In LRng If InStr(1, S.Value, F.Value) = 1 And F.Value <> "" Then S.Interior.Color = F.Interior.Color Exit For End If Next Next Application.ScreenUpdating = True End Sub でしょうか。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

当方の実力を考慮した色んな「忖度回答」ありがとうございました。

  • 回答No.2
  • imogasi
  • ベストアンサー率27% (4675/16766)

後続の回答者などのために、補足を勧める。 >コード中で指定出来れば コードとは、VBAの(スクリプトの)コードですか? 質問者はVBAを、少しはやった経験があるのか? 会社は使用を許すのか? 仕事でエクセルを使い、珍奇な課題を持ち込むと、まずはVBAでできないかということになるのが常。  思うには、こういう複数色の設定など必須か?色の判別はむつかしいし、逆に意味の導出も簡単でもないだろう。例 黄色は何の意味だったけ? ーー エクセルの操作で、「条件付き書式」で、条件を関数で指定する場合は、 エクセルの条件付き書式でセルに色を付ける場合、条件によって、別の色を付けるのは、色ごとに、複数の設定をしないといけない。 それでも質問者のやっている操作(質問文で、現在どうしているか、説明がないようで、おかしい。質問では、条件付書式という語句も出てきてない。現在よりも楽であれば、それ(操作による設定)を使えばよいのでは。 昔は、設定色は3色までだったが、いまのバージョンなら、制約ははずれ、10以上でも設定できるだろう。 >シートによってマチマチ 手数的に、いやになる要素だ。 VBAでやるにしても、一元化は難しそう。 ーーー 「リスト」という言葉は、適当に使ってほしい。 条件となる文字列は、複数あり、同列の複数セル(セル範囲)にに集まっている、だけだろう。 コンピュターの用語の場合、入力規則のリストとか、リストボックスとか(他言語のリスト)特別な意味を持たされるのが現状で、本件では標題を読んで、身構えたが、実は、文字列が複数あり、各文字列は各セルに並んでいる、というぐらいの意味のようだな。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ご指摘ありがとうございました。 今後の参考にさせて頂きます。

関連するQ&A

  • Excelの「リスト」でセルに”着色”したいんだけど!

    Excel2003を使用しています。 データ-->入力規制-->リストでセルに「設定文字(5種類)」を入力していますがそれと同時にそれぞれの色5種類をセルに「着色」したいのですが出来ますでしょうか?宜しくお願いします。

  • リストボックス文字を連結しセルへ入力

    左上入力表にユーザーフォームリストボックスより選出実行された文字をB列にはリストボックス1の選出文字をC列にはリストボックス2、3、4から選出された文字を連ねて入力したいのですが。 尚、行に記入があれば次の空白から順に入力したい。 どなたかコードが解る方よろしくお願いします。

  • 文字列リストから文字列リストの抽出

    A列のリストの文字列に、B列のリストの文字が含まれていたらそれを取り出すにはどうしたらよいでしょうか? ※画像を参照してください。 例:A2セルの文字列にB2~B4の文字が含まれていないかをチェック、含まれていた文字列(B2~B4の内容)をC列に表示する。 関数なら何とかできますが、マクロの知識がほとんどありませんのでマクロが必要になるようでしたら詳しく教えていただければ幸いです。 よろしくお願いいたします。

  • セルの着色に関するVBA

    初めまして、エクセルのVBAの記述方法について質問させてください…! 列A・B・D・Eには文字などのデータがあり、列Cにグループの番号(0~5番グループまで存在します。)列Fに数字が入っている表があります。 表はそれぞれのグループごとにF列の大きい数字から順番に並んでおり、同じ数字の情報が存在する場合、同じ数字ごとに分かれています。 この表を、数字が2回変わると着色したいと思っています。(数字の変化に規則性はありません。) 例えば、100・90・88・87という数字があるとすれば、 数字が100の情報(列A~F)には着色なし、90の情報には着色する、 88の情報には着色なし、87には着色する…といった流れです。 グループが変われば処理のやり直しで、また着色なし→着色あり→着色なし… の作業を全てのグループの表に行いたいです。 説明が分かりづらく申し訳ありません、どなたかご教示いただけると嬉しいです、よろしくお願いいたします。

  • 文字列が同じ場合、セルに色を塗りたい

    エクセル VBAでリストにある文字列と他のセルの文字列が末尾まで完全に同一の場合、 セルに色を塗るにはどのようなコードを記入すればよろしいでしょうか? 具体的には下記のような動きができればと考えております。 G列にリストがあります。(G列のリストの数は変動します) このリストの文字列とB2:B7とE2:E7の範囲の文字列が末尾まで完全に同一の場合、 B2:B7とE2:E7のセルに黄色で色を塗る。 また、リストにはあるけど、B2:B7とE2:E7のセルにない文字列がある場合、 リストのその文字列があるセルに緑で色を塗る。 上記のような動きをするには、エクセル VBA でどのようなコードを記入すればよろしいでしょうか? エクセルは2010及び2007を使用しております。 よろしくお願いします。

  • 【excel2003 vba】指定した文字列が入力されている「セル範囲」の表示方法?

    ◎Sheet1  A B C D E 1○○○-- 2○○○×× 3---×× 4×○--- ※「-」は空白 上記のようにセルに「○」「×」が入力されている「Sheet1」シートがあります。(例として○×の2種類を使っていますが、本当はもっとたくさんの種類の文字列があります。) vbaを使って、以下の一覧表を「List」シートに作成するコードを作成できませんでしょうか? ◎List  A     B 1○     ×  '文字列の種類 2A1:C2  D2:E3 '文字列の範囲  3B4     A4  '同上 【ToDo】 (1)1行目に文字列(○、×)を入力する (2)1行目に入力してある文字列が入力されているすべての「セル範囲」を2行目以降の各列に抽出する。 **1セル内に「○」「×」の両方が入力されているものもある。**  ⇒例えば、A1セルに「○×」と入力されていたら、「Rist」シートのA列B列の両方に「A1」が抽出されるようにしたい。 1セルごとのセル番地(○:A1,A2,B1,…)を一覧化することはできるのですが、同じ文字列をまとめた「範囲」の抽出ができないのです。 どなたかお力添えをお願いできませんでしょうか? 宜しくお願い致します。

  • EXCEL表の文字列が対象セル内に含まれるか

    シートAのB2の文字列が000-みかん_東京_001とあるとして、 シートBのB2:りんご、B3:みかん、B4:いちご C2:横浜、C3:東京、C4:名古屋 D2:赤、D3:青、D4:白 という表があるとします。 シートAのB2セルにシートBのB列とC列から両方含まれる行を探し、D列の値を取得したいのですがどのような関数を利用するのが望ましいでしょう? ちなみに、シートAのB2が調査文字でC2を答えとして、調査対象の行を追加していきたいと思ってます。 お知恵をおかしいただければ幸いです(^^)

  • 表の並び替えと文字列統合

    Excel2007にて _|A |B |C | 1|あ|い|う| 2|え|お|か| 3|き|く|け| のような表があったとします。 この表をセル1列、もしくはテキストで あ,(特定文字列),A1 い,(特定文字列),B1 う,(特定文字列),C1 え,(特定文字列),A2 お,(特定文字列),B2 ・ ・ このように出力する方法を教えて頂けますでしょうか?

  • エクセルで表の条件によってセルを着色する方法

    こんにちは。 エクセルについて質問です。 添付してあります画像のような表があると仮定します。 A~Cの行に対応する数字が3つずつ入っている表です。 私がやりたいのは、この各項目の行に対応する3つの数字が、 どれか一つでもある値以上(以下)となるセルがある場合、 A、B、Cのセルの背景の色を変えたいのです。 たとえば、例の表を用いると、 A、B、Cの各項目について、対応する数値のセルに 30以上の値が入っているセルがひとつでもある項目では項目のセルに色がつくといった形です。 つまり、例の表では、AとBがかかれているセルの背景が着色されます。 条件付き書式では、数値そのもののセルにしか着色できないと思うのですが、 やりかたが分かる方がいらっしゃいましたら、ご教授ください。 よろしくお願いします。

  • エクセルでリストを使って特定の文字列を数える

    エクセル2003を使っています。 シート3に A B C というリストAと D E F というリストB そして A B C D E F と一緒になっているリストCを作りました。 そしてシート1にリストCを使ってこのような表を作りました。 A D A C B D F E C B A B と選択したとします。 そのとき、左側にリストAの中に含まれている文字列を数える方法はないでしょうか。 使っているのは、 Windows XP Professional SP2 Microsoft Office Excel 2003 SP3 です。