マクロ 列ごとの条件付き書式

質問

マクロで列ごとの条件付き書式を作成したいです
条件としては
・列ごとに上限値が1行目に下限値が2行目に入力されている
・3行目以降に測定値が入力される
・その3行目位以降の測定値のセルの書式を上下限値から外れた値は色を変えたい
・50列ほどデータがある

（例）
A1　10
A2　5
A3～A100　数字
条件をA3～A100に設定
A1（10）より大きいもの、A2（5）より小さいものはセルの色を黄色

B1　15
Ｂ2　7　
Ｂ3～Ｂ100　数字
条件をＢ3～Ｂ100に設定
Ｂ1（15）より大きいもの、Ｂ2（7）より小さいものはセルの色を黄色

こんな感じで列ごとにデータと規格値が入っているものが50列あります。

どうかよろしくお願い致します。

回答No.4

tsubuyuki

> 列が50列と限らず100列あったとしても
> 3行目以降のデータもデータ数を気にする事無く
> 自動的にカウントして処理できないですかね？

出来てるはずですけどね。

まぁ、出来なかったようなので別案。
１行目に何かが入力されている列に対し、
３行目以降に何行あっても、全てのセルに条件付書式をつけて回ります。

Sub Sample()
Dim myRow As Long, myCol As Long
　　　　For myCol = 1 To Cells(1, Columns.Count).End(xlToLeft).Column
　　　　　　　　For myRow = 3 To Cells(Rows.Count, 1).End(xlUp).Row
　　　　　　　　　　　　With Cells(myRow, myCol)
　　　　　　　　　　　　　　　　　If .Value <> "" Then
　　　　　　　　　　　　　　　　　　　　　.Interior.ColorIndex = xlNone
　　　　　　　　　　　　　　　　　　　　　.FormatConditions.Delete
　　　　　　　　　　　　　　　　　　　　　.FormatConditions.Add Type:=xlExpression, _
　　　　　　　　　　　　　　　　　　　　　　　　　Formula1:=Application.Or(.Value < Cells(1, .Column), .Value > Cells(2, .Column))
　　　　　　　　　　　　　　　　　　　　　.FormatConditions(1).Interior.ColorIndex = 6
　　　　　　　　　　　　　　　　　End If
　　　　　　　　　　　　End With
　　　　　　　　Next myRow
　　　　Next myCol
End Sub

重ねますが、わざわざマクロを組むまでも無いような気はします。

> 50列あると、なぜか横列に条件がコピーもできず

絶対参照・相対参照の区別はついてますか？

例えばＡ列に条件付書式を設定するなら、
まずは範囲指定をして、Ａ３にフォーカスがある状態で
　　式：=OR(A3<A$1,A3>A$2)
　　書式：黄色塗りつぶし
としてやれば、列方向でも行方向でも書式をコピーして行けば
ちゃんと設定されるはずです。

ここで、
　　式：=OR(A3<$A$1,A3>$A$2)
としてしまうと、列方向にコピーしてもＡ列との比較をしてしまいます。
おそらく、コレだと思いますよ。

お礼

ありがとうございます。
マクロを使用せずコピーしてできました、絶対参照・相対参照は理解していますがコピーした先で式：=OR(A3<$A$1,A3>$A$2)が変わっていないので、うまいことコピーできていないと思い込んでしまいました。
マクロもありがとうございます
助かりました。

回答No.3

tsubuyuki

前お二人同様、普通に条件付書式で設定してやれば早いのではないかなぁ、と思います。

が、なんとなく。

こんなやりかたでも出来ます。

Sub Sample()
Dim myRange As Range
Dim myCell As Range

　　　　Set myRange = Range(Range("A3"), ActiveCell.SpecialCells(xlLastCell))
　　　　myRange.Interior.ColorIndex = xlNone
　　　　For Each myCell In myRange
　　　　　　　　If myCell <> "" And myCell < Cells(1, myCell.Column) Or myCell > Cells(2, myCell.Column) Then
　　　　　　　　　　　　myCell.Interior.ColorIndex = 6
　　　　　　　　End If
　　　　Next
End Sub

マクロで「条件付書式を設定」してやるとすると
> 上下限値から外れた値は
ですから「下限値（１行目）より小さい、または、上限値（２行目）より大きい」だと思うので、

Sub Sample2()
Dim myRange As Range

　　　　Set myRange = Range(Range("A3"), ActiveCell.SpecialCells(xlLastCell))
　　　　Range("A3").Select
　　　　With myRange
　　　　　　　　.Interior.ColorIndex = xlNone
　　　　　　　　.FormatConditions.Delete
　　　　　　　　.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="=A$1"
　　　　　　　　.FormatConditions(1).Interior.ColorIndex = 6
　　　　　　　　.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=A$2"
　　　　　　　　.FormatConditions(2).Interior.ColorIndex = 6
　　　　End With
End Sub

または

Sub Sample3()
　　　　Range("A3").Select
　　　　With Range(Range("A3"), ActiveCell.SpecialCells(xlLastCell))
　　　　　　　　.FormatConditions.Delete
　　　　　　　　.FormatConditions.Add Type:=xlExpression, Formula1:= "=OR(A3<A$1,A3>A$2)"
　　　　.FormatConditions(1).Interior.ColorIndex = 6
　　　　End With
End Sub

こんな感じで設定できると思いますよ。

マクロを使うべきなのか、使わずにやるのか、そこは判断次第ですが、
マクロを組むと「条件が変わったときに」そこそこ面倒です。
例えば「Ａ１セル：下限値、Ａ２セル：上限値、Ａ３以降：試行回数」などの見出しを付けると、
それに合わせてマクロを書き換えてやる必要があります。
ここで（私の分に限らず）マクロを提示されて、貼り付けて使うのはかまいませんが、
「何をどう処理しているのか理解して」使わないと、後のメンテナンスは困難です。

という観点からも、「わからないマクロの濫用」はオススメしません。

補足

ありがとうございます
マクロを使わなくても設定できるんですが
50列あると、なぜか横列に条件がコピーもできず
とても面倒なので、マクロだったら簡単に処理できないか？と思いまして
質問しました。

A列3行目以降データの上下限規格はA1・A2で
B列3行目以降データの上下限規格はB1・B2なので

教えてもらったものを処理させると
A1・A2の上下限規格だけで全部のデータを色付けしてしまいました。

列が50列と限らず100列あったとしても
3行目以降のデータもデータ数を気にする事無く
自動的にカウントして処理できないですかね？

わがままな質問で申し訳ありません。

回答No.2

keithin

わざわざマクロとか使わなくても、一般操作だけで十分できます。

ご利用のエクセルのバージョンも不明のご質問なので仮にＥｘｃｅｌ２０１０の場合

簡単な手順：
Ａ３：ＡＸ１００を選択
条件付き書式▼からセルの強調表示ルールで「指定の値より大きい」を開始
=A$1
と記入、書式でセルを黄色く塗る

改めて条件付き書式▼からセルの強調表示ルールで「指定の値より小さい」を開始
=A$2
と記入、書式でセルを黄色く塗る
必要に応じて同じくＡ３：ＡＸ１００について、セルの値が空っぽだったらセルの色を塗らないを優先して取り付けておくのも良い方法です。

別の方法：
Ａ３：ＡＸ１００で条件付き書式▼から「新しいルール」を開始
指定の値を含むセルだけを書式設定で「次の値の間以外」を選択、=A$2　と　=A$1　を設定
書式でセルを黄色く塗る

どーしてもマクロを使いたい場合、新しいマクロの記録でも使えるマクロは簡単に採取できます。ただしご利用のエクセルのバージョンによって同じマクロが「使えない」ので、十分注意が必要です。

全バージョン共通：
sub macro1()
　range("A3:AX100").formatconditions.delete
　range("A3:AX100").formatconditions.add type:=xlcellvalue, operator:=xlequal, formula1:="="""""
　range("A3:AX100").formatconditions.add type:=xlcellvalue, operator:=xlnotbetween, formula1:="=A$2", formula2:="=A$1"
　range("A3:AX100").formatconditions(1).interior.pattern = xlnone
　range("A3:AX100").formatconditions(2).interior.color = vbyellow
end sub

回答No.1

tom04

こんにちは！
単に条件付き書式ではダメですか？

Excel2007以降のバージョンをお使いであれば
A3～最終データを範囲指定　→　条件付き書式　→　新しいルール　→　数式を使用して・・・　→　数式欄に
=IF(A3<>"",OR(A3>A$1,A3<A$2))

という数式を入れ　→　書式　→　塗りつぶしから「黄色」を選択しOK
これでご希望通りになると思います。

どうしてもマクロで行いたい場合は、安直な方法ですが一例です。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。

Sub 色付け() 'この行から
Dim i As Long, j As Long, k As Long
j = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
Range(Cells(3, 1), Cells(100, j)).Interior.ColorIndex = xlNone
For k = 1 To j
For i = 3 To Cells(Rows.Count, j).End(xlUp).Row
If Cells(i, k) <> "" Then
If Cells(i, k) > Cells(1, k) Or Cells(i, k) < Cells(2, k) Then
Cells(i, k).Interior.ColorIndex = 6
End If
End If
Next i
Next k
Application.ScreenUpdating = True
End Sub 'この行まで

※　マクロの場合、条件付き書式が設定してあると色が変化しませんので、条件付き書式の設定は解除しておく必要があります。

※　マクロではデータ変更があるたびにマクロを実行しなければなりません。

参考になりますかね？m(_ _)m

補足

ありがとうございます
色つけなので、上限値を変更した場合は、マクロをもう一度走らせることが必要ななんですね。
でも、ボタン１つあれば確認できるのでありかもしれません
しかし、わがままを言えば条件付き書式を設定したいな・・・

すみません、わがままで