EXCEL VBA 特定の行に色をつけたい

質問

お世話になります。田中と申します。

金額の差異チェックするシートがあります。A～Z列まで各項目があり行は可変です。
データはA2から始まります。

Z列に[結果]という項目があります。
この結果が"NG"の場合はその行全体の文字を赤に塗る処理をVBAで自動化したいのです。

現在は手動で[結果]列にオートフィルターをかけて該当行に色付けしているため結構手間がかかります。
理想はこのシートに[判定]というボタンをおいてボタンを押したら上記の処理が実施されると嬉しいです。

どなたまお知恵をお借りできますでしょうか。
よろしくお願い致します。

EXCEL2013

回答No.4

keithin · ベストアンサー

データを記入したところから開始する
（データの修正、書き換え、塗り直しは考慮しない）

sub macro1()
　dim h as range
’cells.interior.colorindex = xlnone

’ＮＧの行を調べて色を塗る
　set h = range("Z:Z").find(what:="NG", lookin:=xlvalues, lookat:=xlwhole)
　if h is nothing then exit sub
　do until h.interior.color = vbred
　　h.entirerow.interior.color = vbred
　　set h = range("Z:Z").findnext(h)
　loop
end sub

お礼

keithin さん、ご連絡ありがとうございました！

ご教授いただきましたロジックで思い通りの動きが実現できました!!
いつもお助けいただきまして本当にありがとうございます! ^^

回答No.3

kagakusuki

>[結果]列にオートフィルターをかけて該当行に色付け

という処理を行うだけでしたら、以下の様なVBAとなります。

Sub QNo8979194_EXCEL_VBA_特定の行に色をつけたい()

Dim FirstRow As Long, LastRow As Long, FirstColumn As String _
, LastColumn As String, RefColumn As String

FirstRow = 1 '表の項目欄となっている行(複数行ある場合には、データが始まっている行の1つ上の行)
FirstColumn = "A" '表の左端の列
LastColumn = "Z" '表の右端の列
RefColumn = "Z" '"NG"が入力される列

Application.ScreenUpdating = False
With ActiveSheet
If .Columns(RefColumn).Find(what:="NG", LookIn:=xlValues, lookat:=xlWhole) Is Nothing Then Exit Sub
LastRow = .Range("Z" & RowS.Count).End(xlUp).Row
.Range(RefColumn & FirstRow & ":" & RefColumn & LastRow).AutoFilter Field:=1, Criteria1:="NG"
.Range(FirstColumn & FirstRow + 1 & ":" & LastColumn & LastRow) _
.SpecialCells(xlCellTypeVisible).Interior.Color = 255
.Columns(RefColumn).AutoFilter
End With
Application.ScreenUpdating = True

End Sub

　又、VBAのみを使って
「Z列に何かを入力した瞬間に、Z列に"NG"と入力されている行のみが赤く塗りつぶされ、Z列に入力されている値を"NG"以外に変更すると、その行の塗りつぶしが消える」
という条件付き書式と似た様な処理をさせる場合には、以下の様なVBAを、標準モジュールではなく、「Z列に"NG"と入力されるセルが存在しているシート」のシートモジュールに入力して下さい。

Private Sub Worksheet_Change(ByVal Target As Range)

'QNo.8979194 EXCEL VBA 特定の行に色をつけたい

Dim RefColumn As String

RefColumn = "Z" '"NG"が入力される列
If Intersect(Target, Columns(RefColumn)) Is Nothing Then Exit Sub

Application.ScreenUpdating = False
Dim FirstRow As Long, LastRow As Long, FirstColumn As String _
, LastColumn As String

FirstRow = 1 '表の項目欄となっている行(複数行ある場合には、データが始まっている行の1つ上の行)
FirstColumn = "A" '表の左端の列
LastColumn = "Z" '表の右端の列

LastRow = Range("Z" & RowS.Count).End(xlUp).Row
If LastRow <= FirstRow Then Exit Sub

RowS(FirstRow & ":" & LastRow).Interior.Pattern = xlNone
Range(RefColumn & FirstRow & ":" & RefColumn & LastRow) _
.AutoFilter Field:=1, Criteria1:="NG"
Range(FirstColumn & FirstRow + 1 & ":" & LastColumn & LastRow) _
.SpecialCells(xlCellTypeVisible).Interior.Color = 255
Columns(RefColumn).AutoFilter
Application.ScreenUpdating = True

End Sub

　後、条件付き書式の設定をVBAを使って行うという方法も確かにありなのですが、ただ単に条件付き書式を付け加えるだけのマクロにしたのでは、マクロを作動させる度に、前回設定された条件付き書式と同じ条件付き書式が無駄に1つずつ増えて行く事になりますから、それを防ぐためには前回設定した同じ条件付き書式を消去するなどといった工夫が必要になります。
　だからと言って、単純に既存の条件付き書式を全て削除してしまったのでは、複数の条件付き書式が設定されていて、尚且つそれらの中には残しておきたいものがある場合であっても、マクロを作動させた際に全て消えてしまいますので、条件付き書式を全て削除してから入れ直すという方法はあまりお勧めできません。
　そのため、もしVBAで条件付き書式を設定させる様にするには、既存の条件付き書式の中に、VBAで設定しようとしているものと同じ働きをするものがあるかどうかを調べて、同じ働きをするものだけを削除する様にしなければなりません。
　その条件付き書式を調べるための構文を考えるのが面倒でしたので、今回の回答では、条件付き書式を設定するVBAに関しては提示致しません。

お礼

kagakusuki さん、ご連絡ありがとうございました！

詳細なロジックととても細かなご説明をいただきまして誠にありがとうございます！
特ににご説明文はロジックを考えるうえでの知識としてとてもタメになりました。

今回ご教授いただきましたロジックで思い通りの動きが実現できました!!
このたびは本当にありがとうございます! ^^

回答No.2

mt2008

VBAに詳しい人がいないなら余計に条件付き書式を使用した方が良いように思いますが……。

以下の例ではVBAでアクティブシートに条件付き書式を設定しています。
こんなのも駄目ですか？

Sub Sample()
　　With Columns("A:G") 'アクティブシートのA:G列が対象
　　　　.FormatConditions.Delete '条件付き書式をクリア
　　　　.FormatConditions.Add Type:=xlExpression, Formula1:="=($G1=""NG"")" '条件
　　　　.FormatConditions(1).Interior.Color = 255 '書式（赤く塗りつぶし）
　　End With
End Sub

お礼

mt2008さん、ご連絡ありがとうございました！

確かに仰る通りなのかもしれません。。
私の会社は何でも伝統的(?)にVBAで開発しているようでして。。

今回ご教授いただきましたロジックで思い通りの動きが実現できました!!
いつもお助けいただきまして本当にありがとうございます! ^^

回答No.1

mt2008

補足願います。
条件付き書式でやらない理由は何でしょう？
その理由によってはコードを作る際に注意が必要になります。

補足

mt2008 さん早速のご連絡ありがとうございます！

このEXCELは基幹システムのメニューをたたくとデータが吸い上げられて自動作成されるのですが、項目追加やフォーマットを変える場合はメニュー部分に組み込まれているマクロにこういったロジックを追加することで対応しているためです。。
ただまわりにVBAに詳しい人間がいないため、私がこのように皆様にお聞きしてロジックを既存のマクロに追加して対応しているためです。

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