塗りつぶしで色付けしたセルの１行を同じ色にしたい

質問

いつも、いつもお世話になりありがとうございます。
下図のC列（C4～C34）に「セルの塗りつぶし」で色を付けた（３～５色ほど予定）セルと同じ色で
１行（D列～AG列）を塗りつぶす方法はないでしょうか？

しかも、条件付き書式で設定したセルは下図の４行目のように、その書式の色を
そのまま残したいのです。

途中、空白のセルも有ります。

条件付き書式の設定を使って「数式を・・・」「文字」「値」などを利用する方法は
検索して出てくるのですが、C列は任意で言葉を入れ、「セルの塗りつぶし」で着色します。

ですから、条件は
1.C列のセルの色を読み取り、その右側の１行（D列～AG列）を同じ色にする。
2.条件付き書式で設定したセルは、その書式の色をそのまま残す。
3.途中、値も関数式も文字も何もない空白のセルもある。

そんな都合の良い方法は有りますでしょうか？
もし有りましたら、どうかご教授いただけないでしょうか？
よろしく、お願い致します。

回答No.9

tom04 · ベストアンサー

おおよその表のレイアウトが判ってきました！
すでにWorkSheetChangeイベントのマクロがあったわけですね！

それでは二つのマクロを一つにまとめてみてはどうでしょうか？
（せっかくのマクロがあるのでそれをそのまま利用させてもらっています。）

↓の画像のように「マクロリスト表」SheetのA列を使わせてもらいます。
A列に操作したいSheetのC列に入力予定データを入力
（1）「マクロリスト表」SheetA列セルを塗りつぶしたい色に塗りつぶしておく
（2）濃い色で文字が見にくい場合は「マクロリスト表」A列フォント色も変えておく
（3）縦列の条件付書式だけは設定しておく（他の列の条件付書式はすべて削除！）

以上の準備ができたうえで、すべてのコードを削除して↓のコードをコピー＆ペーストしてみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Intersect(Target, Range(Cells(4, "C"), Cells(34, "AG"))) Is Nothing Or _
Selection.Count <> 1 Then Exit Sub
Dim i, j, k As Long
Dim ws As Worksheet
Set ws = Worksheets("マクロリスト表")
i = Target.Row
If Target.Column = 3 Then
If Target <> "" Then
Range(Cells(i, "C"), Cells(i, "AG")).Interior.ColorIndex = xlNone
If WorksheetFunction.CountIf(ws.Columns(1), Target) Then
k = WorksheetFunction.Match(Target, ws.Columns(1), False)
Range(Cells(i, "C"), Cells(i, "AG")).Interior.ColorIndex = _
ws.Cells(k, "A").Interior.ColorIndex
End If
Else
With Range(Cells(i, "C"), Cells(i, "AG"))
.Interior.ColorIndex = xlNone
.Font.ColorIndex = xlAutomatic
End With
End If
End If
Dim h As Range
On Error Resume Next
For Each h In Target
If h <> "" Then
Application.EnableEvents = False
h = Application.WorksheetFunction.VLookup(h.Value, ws.Range("D:E"), 2, False)
Application.EnableEvents = True
End If
Next
If Target = "" Then
Target.Interior.ColorIndex = Cells(i, "C").Interior.ColorIndex
End If
For j = 3 To 33
If Cells(i, "C") = ws.Cells(k, "A") Then
Cells(i, j).Font.ColorIndex = ws.Cells(k, "A").Font.ColorIndex
End If
Next j
End Sub 'この行まで

※　こまかい検証をしていませんので、
また不具合が出たらごめんなさいね。m（_ _）m

お礼

最後まで、無理なお願いにお答え頂き、ありがとうございました。
お陰様で、良い物ができそうです。
あと一息のところまで、なんとかこぎつけるところです。
（といいながら、また、次の欲求が出てきますが…。）

これで、ようやく初心者の仲間入りが出来るようです。
（今までは、赤ちゃんの域でした（￣ー￣；ヒヤリ））
ほんとうに、ありがとうございました。

回答No.8

tom04

交換日記状態ですが・・・(ーー;)

縦列が赤の件について！
＞A列、V列とあと２列だけ、縦に赤でぬりつぶされています・・・
とありますが、
C～AG列のみマクロで色操作するようにしていますので、それ以外の列はそのままでも大丈夫だと思います。
さて、範囲内の縦赤色の問題

前回のコードでは範囲内に変更があれば、範囲内のその行の色を一旦クリアするようにしていますので
方法としては

（1）その列だけ条件付書式を設定する。
（2）コード内に条件付書式の条件を付け加える
のどちらかが考えられると思います。

その列が決まっているのであれば（1）の方をオススメします。
何度も書いているように「条件付書式で色付されているセルはマクロでも色変更ができない！」
ということを逆手に取る方法。

（2）の方も当然可能ですが、その条件がこちらでは判断しかねますのでコードを訂正しようがありません。

今回は、この程度で・・・m（_ _）m

補足

度々、お世話になりありがとうございます。
訳あって、戻って参りましたので早速試してみましたが、
何の変化も応答もありません。
試しに新規のBOOKを開いて、試してみますと見事におっしゃる通りの結果が再現されました。
そこで、両者の違いを色々検討しますと、

今回教えて頂いたマクロを挿入したシートに関して、もう一つ下記に示すマクロが有ることです。
他のマクロが影響するということも有るのでしょうね？
ちなみに、このマクロはセルに数字を入力し、対応した○、◎、早・・・などの文字や記号を呼び出すものです。

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim h As Range
Set Target = Application.Intersect(Target, Range("D4:K34, M4:U34, W4:AA34, AC4:AG34"))

On Error Resume Next
For Each h In Target
If h <> "" Then
Application.EnableEvents = False
h = Application.WorksheetFunction.VLookup(h.Value, Worksheets("マクロリスト表").Range("D:E"), 2, False)
Application.EnableEvents = True
End If
Next
End Sub

念のため、このマクロを削除して、再度挑戦してみましたが、やはり何の反応も有りません。

そこで、このBOOK全てのマクロを削除し、今回のマクロだけを投入し試行したところ、見事、成功したのですが、
「実行時エラー’１００４’WorksheetFunctionクラスのMatchプロパティの取得ができません。」
というメセージが表れ、デバックしてみると教えて頂いたマクロの記述の１６行目の

k = WorksheetFunction.Match(Cells(i, j), ws.Columns(2), False)

が黄色く塗られていました。

このメッセージが出るのは、C列のみで数字を入れても、文字を入れても出ます。

もう、私ではどうにもなりません。
せっかく、すべてのマクロを消してしまわないと反映しないとは言え、一歩前進と喜んでいたのですが。
どのマクロのどこが影響しているのかも検証しなければなりませんが　＞＜；

それと、こんなところで更にお聞きするのも心苦しいのですが、セルの着色は出来ましたが、色によっては文字を白文字にしないと見えなくなりますので、文字の色も変えられないでしょうか？

わかる範囲で構いません。
お手すきの時にでも、お助け下さいませんか。

どうか、よろしくお願いいたします。m(__)m

回答No.7

tom04

またまた参上です。
No.5の補足の件について・・・

Excel2003以前のバージョンだと条件付書式の設定は3つまでしかできませんので
マクロになってしまいます。

何度も書いているように条件付書式が設定されているセルの色はマクロでも変更できませんので
すべての条件付書式は削除した上でのコードを載せてみます。

IFで分岐しても良いのですが、今回は↓の画像のようにSheet2のA列に
C列データを入力し、セルを好みの色に塗りつぶしておきます。
B列はD～AG列のデータを入れて同様に塗りつぶしておきます。
以上の準備ができたうえでのコードです。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてC4～AG34のデータを色々いれてみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Intersect(Target, Range(Cells(4, "C"), Cells(34, "AG"))) Is Nothing Or _
Selection.Count <> 1 Then Exit Sub
Dim i, j, k As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2") '←「Sheet2」は実際のSheet名に！
i = Target.Row
If Target.Column = 3 Then
Range(Cells(i, "C"), Cells(i, "AG")).Interior.ColorIndex = xlNone
If WorksheetFunction.CountIf(ws.Columns(1), Target) Then
k = WorksheetFunction.Match(Target, ws.Columns(1), False)
Range(Cells(i, "C"), Cells(i, "AG")).Interior.ColorIndex = _
ws.Cells(k, "A").Interior.ColorIndex
End If
For j = 4 To 33
If WorksheetFunction.CountIf(ws.Columns(2), Cells(i, j)) Then
k = WorksheetFunction.Match(Cells(i, j), ws.Columns(2), False)
Cells(i, j).Interior.ColorIndex = ws.Cells(k, "B").Interior.ColorIndex
End If
Next j
Else
If Target <> "" And WorksheetFunction.CountIf(ws.Columns(2), Target) Then
k = WorksheetFunction.Match(Target, ws.Columns(2), False)
Target.Interior.ColorIndex = ws.Cells(k, "B").Interior.ColorIndex
Else
Target.Interior.ColorIndex = Cells(i, "C").Interior.ColorIndex
End If
End If
End Sub 'この行まで

※　必ず条件付書式の設定はすべて削除しておいてください。
※　このような方法だともっとたくさんの色付ができます。
※　この場合だと手作業で色変更も可能です。

これで何とかご希望通りにならないでしょうか？m（_ _）m

お礼

遅くまで、お手数をおかけして、ありがとうございました。
しばらく所要で出かけております。
帰り次第、早速試して見たいと、楽しみに思っております。
本当に、便利のいいのを教えて下さってありがとうございました。
また、出来次第連絡させて頂きます。

補足

度々で恐縮です。
今、出先なものでまだ、マクロを試せないままなのですが、ふっと心配が浮かんでしまいました。
最初の質問で参考にアップしました見本のシートを見ていただくと、A列、V列とあと２列だけ、縦に赤でぬりつぶされています。このセル列は、条件指定で５以下だと赤になり、V列だと６以下で赤になると言う様に、敷居値で条件設定しています。他の２列も同様です。今後も、その敷居値は変更する予定があります。

１ｓｈｅｅｔ２に敷居値の設定が出来るのでしょうか？その場合、どの様に表現すればよいのでしょうか？

２.或いは、この列だけをマクロの範囲指定から除き、４列だけ条件付き書式の設定をした方が良いのでしょうか？
以前この質問コーナーの他の質問で教えてもらった範囲指定は

Range("C4:K34,M4:U34,W4:AA34,AC4:AG34").Select

などと記述してありますが、範囲指定で４列をマクロの適用から除く場合、お示し頂いているマクロの記述の「どこ」を「どの様」に書き換えればよろしいのでしょうか？

今後の勉強のためにも、出来ましたら上記２つとも教えていただけないでしょうか？

まだ、先に教えていただいている内容も実行しないままで申し訳く、厚かましいお願いですが、よろしくお願いいたします。

回答No.6

tom04

たびたびお邪魔します。

No.4の補足の件ですが・・・

条件付書式の設定だけでは普通そんなメッセージが出ることはあいません。
全く反応しないか、的外れな部分に色がつくくらいです。

ん～～～
原因は判断しかねますが、
マクロか何かが設定してありませんか？
確認の意味で

画面左下の操作しているSheet見出し上で右クリック　→　コードの表示　→　VBE画面がでますので
そこに何かコードが入っていればマクロが設定してあることになります。
もしコードが入っていればすべて削除してください。

前回も書いたように条件付書式の色付とマクロの色付は共存できない！
と思った方が良いです。

今考えられる原因とすればこのくらいですが
他の原因ならごめんなさいね。m（_ _）m

補足

ちなみに、セルの塗りつぶしは全て条件付き書式で行なっております。
数字入力で色々の項目を呼び出すのにマクロを使ってはいますが。

先程は、新規のワークシートで試しに設定してみたところ、あのようなコメンが出るようになったのですが、実際のワークシートでは全くでませんでした。
ご心配をお掛けしました。
しかし、原因不明と内容が不明なのがちょっと、恐怖です。

あと、No5の補足でお尋ねしました件、どうかよろしくお願い致します。

回答No.5

tom04

No.3・4です！
No.3の補足に
C列のカラーインデックスを・・・
という内容がありますが、
手元のExcel2003では条件付書式で設定してある色はマクロでも変更できません。
（Excel2007以降のバージョンでも同様だと思います。）

すなわち、条件付書式だけで色を表示・表示させるのか？
マクロだけで色を変更（着色）させるのか？

のどちらかに統一する必要があります。

当然条件付書式の数式に　カラーインデックス番号を入力してもエラーになるか、
全く意味のないものになってしまうと思います。m（_ _）m

補足

申し訳ありません。私のようにセルを塗りつぶす事で検索していますと、カラーインデックスを使った手法がネットで出ていたものですから、ついその必要が有ると思い込んでしましました。しかし、今回、No4のご回答の通りで思ったことができましたので、何もいうことは有りません。
それと、No,3の補足欄で書きました

「自動＝条件付きで色が変わっていません。」

も、再度設定し直しますとなぜか、再現しません。

今回の件は無事解決したようです。

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

それから、感謝祭、蚤の市、全館１０％ＯＦＦの３つの項目に対して、同じ条件で行全体を塗りつぶしたいと思うのです。

ところが、作成していますPCはご推察の通り、エクセル2010を使っていますが、実際に使用する環境はエクセル2002が入っていて、どうも調べてみると条件付き書式は3個までの設定しかできないそうです。
そこで、他の条件でも条件付き書式を入れますと、４個になってしまうセルも出てきます。そこで、なるべく条件設定の項数を減らしたいので、１つの条件設定で、感謝祭、蚤の市、全館１０％ＯＦＦの条件を盛りこみたいと思いますが、この場合、数式のどの様に記述すればよいのでしょうか？
またまた、お手数をお掛けしますが、お助けいただけませんでしょうか。
よろしくお願いいたします。

回答No.4

tom04

No.3です！

当方の認識としてはC列に条件付書式が設定してあり、D～AG列もその色に塗りつぶしたい！
という解釈で投稿しました。
その説明がNo.3の内容でした。

お礼欄に
＞C列に「感謝祭」という言葉で灰色の塗りつぶしになるよう・・・
とありますので、おそらくC列だけが条件付書式で色付の設定がしてあるものだと解釈しています。

前回と重複してしまいますが、
とりあえずC列の条件付書式の設定をすべて削除します。
仮にC列が「感謝祭」の場合D～AG列の行に色がつく条件付書式を設定する場合は

C4（必ずC列から）～AG34を範囲指定　→　新しいルール　→　「数式を使用して・・・」　→　数式欄に
=$C4="感謝祭"
として好みの色を選択しOK
これでC列が「感謝祭」の場合はその行すべてに同じ色がつくはずです。

おそらくExcel2007以降のバージョンをお使いのようですので、
「感謝祭」の部分だけの変更で条件はいくつでも設定できるはずです。

最後に前回投稿したように条件付書式の優先順位を決定しておきます。

※　今回も外していたらごめんなさいね。m（_ _）m

お礼

お忙しいところ早速のご回答ありがとうございます。
ご指示の通りにしましたら、選んだところまでの行の塗りつぶしができました。
ありがとうございます。
これで、本当は完結なのですが、C4～AG34のセルを選ぶたびに

「自動＝条件付きで色が変わっていません。」

というコメントが出て来ます。一応「ＯＫ」ボタンが付いていてＯＫしますとコメントは消え、そのまま「感謝祭」と入力でき、思った通りの結果がでます。
しかし、その条件設定の時に選んだすべてのセルのどのセルを選択しても、その度に

「自動＝条件付きで色が変わっていません。」

と出てきて、煩わしいのと、どの様にせよといっているのか、意味もさっぱりわかりません。

どうかお助け下さい。

回答No.3

tom04

こんにちは！

おそらくD4～AG34セルの条件付書式の設定は「セルの値に基づいて・・・」で
色設定されていると思いますので、

＞.C列のセルの色を読み取り・・・
ではなく、C列も含めて行全体の色付の設定をしてはどうでしょうか？

C4～AG34セルを範囲指定　→　新しいルール　→　「数式を使用して・・・」　→　数式欄に
=$C4="条件"
（「条件」の部分はC列の色になる条件をいれます）

もう一度条件付書式　→　「ルールの管理」　→　条件付書式の一覧が表示されますので、
そこで条件付書式の優先順位を決めます。
（上側の行にある条件が優先されます）

一番優先度の高い条件付書式「各セルの色付」を選択　→　画面右上の↑・↓の矢印で
一番上の行に移動させます。

これでなんとか希望に近い形にならないでしょうか？

的外れならごめんなさいね。m（_ _）m

お礼

回答有難うございます。

＞=$C4="条件"
　（「条件」の部分はC列の色になる条件をいれます）

条件にC列のセルの色と同じにするというためにどのような記述をすればいいのかわかりません。

初歩的で申し訳ありませんが、教えていただけないでしょうか？

ちなみに、言葉足らずだったようなのですが、C列に「感謝祭」という言葉で灰色の塗りつぶしになるよう、おっしゃる通り、条件付き書式で設定しております。その他にも２つ設定しております。

それ以外に、C列には、その時々の行事などを文字で入力したりします。その時に行全体を塗りつぶして強調したいので、C列のそのセルを塗りつぶすと自動で隣のセルよりAG列まで一気に行全体が塗りつぶれないかと考えています。

よろしければ、引き続きご教授頂けませんでしょうか。
よろしくお願いいたします。

補足

カラーインデックス（色コード）を以下の記述を、マクロ使って「CellColor というユーザー定義関数を作成しました。

Function CellColor(セル)
CellColor = セル.Interior.ColorIndex
End Function

以上で、カラーインデックス（色コード）を取り出すところまでは、何とか出来ました。

=$C4="=CellColor(C4)"
次にD４に上の様な式を入れてみましたが、何の反応もかえってきません。

初歩の私では、ここまでしか出来ません。
上の式がおかしいかどうかの判断も出来ません。

どうか、引き続きお教え下さい。

回答No.2

nitto3

すみません、途中の抜けるところはやり直しが必要です。

お礼

早速のご返答有難うございます。
マウスで選んで塗りつぶすのを、自動化したいのです。
何か、都合の良い関数かマクロは無いものでしょうか？

有りましたら、どうかご教授下さい。
よろしくお願いいたします。

補足

マウスで選んで塗りつぶすのを、自動化したいのです。
何か、都合の良い関数かマクロは無いものでしょうか？

有りましたら、どうかご教授下さい。
よろしくお願いいたします。

回答No.1

nitto3

行を選んで塗りつぶせばいいじゃないの。