条件付き書式の計算式と適用先範囲

質問

通常のExcel操作による条件付き書式も同じなのですが、VBAで書いているときに疑問が出たので、VBAで質問させていただきます。

図のようにA列とB列の値を比較し、A列とB列の値が同じでない場合、つまり「A列－B列」の値が0でないセルに赤い色をつけることにしました。A列が正で、B列が違っている場合色をつける、という考えかたの処理です。

不思議なのは、手動で条件付き書式画面からやっても、下のコードのVBAでやっても「--A」のところのように、比較式を「"=$A2-$B2<>0"」と、2行目で行っているだけ（比較はここだけ）なのです。

しかし実行結果は、A列とB列の値が同じではない行すべてのB列に色がついています。

B列のセルの同じ行のA列と値が異なる場合、赤い色をセルにつけるのが目的なので、結果としてはこのコード（条件付き書式）は正しく動作しています。

質問は、比較式が「--A」のところ＝2行目にしかないのに、なぜ3行目～20行目も同様の効果が効いているのでしょうか。

この挿入している効果は条件付き書式であるため、B列のセル上の値を左の行（A列）と合致させると、ちゃんと赤い色は消えてくれます。

この謎が解けません。

----------------------
Sub 左列と異なる値は赤に()
'条件書式を挿入
With ActiveSheet
Range("B2:B20").Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=$A2-$B2<>0"　' --A
.FormatConditions(1).Interior.Color = RGB(255, 200, 200)
End With
Range("A1").Select
End With
End Sub

回答No.3

f272 · ベストアンサー

#1です。

補足で「逆にいえば、20行目に=$A20-$B20<>0が自動的に挿入されているのなら、理解できる」なんて言っているところから判断すると，条件付き書式のルールがセルごとに設定されていると思い込んでいるようです。
(あなたの考え)
B2に=$A2-$B2<>0
B3に=$A3-$B3<>0
途中省略
B20に=$A20-$B20<>0

> ほかのセルに設定された条件は
> =$A2-$B2<>0
> となっています（B20セルを選択して条件付き書式の画面で確認）。

ここでも条件付き書式のルールがセルごとに設定されていると思い込んでいることがわかります。条件付き書式はセルには設定されていないのです。セル範囲に設定されています。

この例では条件付き書式のルールはB2:B20に設定されているものだけです。一つしかありません。そしてそのルールの内容が=$A2-$B2<>0です。
(実際の設定)
B2:B20に=$A2-$B2<>0
このときの式の書き方が理解できていないのでしょう。設定範囲がB2:B20と言うように複数セルになっていますが，ルールを書くときに参照するセルは必ず左上のセルです。参照セル以外にルールを適用するときには相対参照の考えを使って式を解釈します。だからB20ではあたかも=$A20-$B20<>0が入力されているかのように振る舞います。

お礼

思い込んでいるというか、条件付き書式を手動でやっていたころから、なんでそうならないのか？という問いかけでした。

＞ルールを書くときに参照するセルは必ず左上のセル
条件付き書式について、この説明をどの本でも見たことがないので疑問でした。

これでストンと胸に落ちました。

回答No.2

imogasi

小生は、数度質問を読んだが、意図がはっきり理解できたと思えない。
しかし推定して、下記のようなことかな？
今までのこのコーナーの条件付き書式に関した質問者（初学者が多いようだ）は、
条件付き書式を考えるとき、下記（１）が多かったように思う。
私が思うに、下記の２つの考え方・やり方があって、
（１）「１つのセル」（書式設定するセル範囲の左上隅１セルの場合がほとんど）に、
条件のための数式を設定して、それを以下（以右）のセル範囲にコピーして、全体のセル範囲に拡張する。
（２）「書式を設定したいセル範囲全体」を範囲指定しておいて（通常は手動操作で）
、範囲指定した左上隅の１セルを念頭に（かつ左上隅の１セルをアクチブセルにしておいてー＞この点重要）（代表して）１セル用の数式を設定する。
私は当然（２）のやり方で考えてきた。
ーー
今回の質問のＶＢＡなどは（２）のやり方により、コードが作成されているもので、
（Range("B2:B20").Select の部分）
書式を設定する範囲のセルの、左上隅のセルを範囲の代表として、数式を設定する
のだ。
他のセルは、エクセルシートの数式の複写の考えで、各セルの数式が拡張設定され、セル番地が適当に自動で変化させられる（してくれる）。
だから、それらの仕組みを逆手に取って、式を考えるべき、ことになる。
変化させるべき場合は行や列の番号や記号の前に＄を付けてはならず、固定すべきセル
には、行や列の番号や記号の前に＄を付けておくべきことになる。
こういうことを理解しておれば、この質問のようなことは、疑問が出ないと思うがいかがかな。
質問者は上記（１）と（２）のうちで、（１）中心の考えで来たものだから、不思議なのかなと思った。
小生が質問の疑問や意図を誤解したかな。
ーー
ＶＢＡの条件付き書式ＦｏｒｍａｔＣｏｎｄｉｔｉｏｎｓは、ＶＢＡでいうＲａｎｇｅ＝セル範囲＝複数セル（といっても近接の塊になったセルだが、とびとびの複数セルは、さらなる拡張形の問題）に対して設定する（できる）ものです。
ＶＢＡではメソッドやプロパティはＲａｎｇｅに対して、設定できるものが多く、この点を
、十分留意すべきなんだ。
エクセル初心者は、シートのセル枠（マス目の集まり）を見て、１セルがシートの構成単位だ、という考えを抱きやすいと思うが、ＶＢＡのシートでは、基本オブジェクトはＲａｎｇｅで、複数セル（ただし四角形の範囲です）を基本単位と考えたＲａｎｇｅの考えは、ＭＳなどのエクセル設計者は、よく考えていて流石、と思うがどうかな。

補足

手動で（Excelのリボンから）条件付き書式を設定しても同じなのですが、条件というか判定式に
=$A2-$B2<>0
と設定しており、比較しているのはA列もB列も2行目だけを記述しています。

条件付き書式の設定する範囲は20行目までも理解しています。

しかしB2セルに条件付き書式設定後オートフィルで20行目まで（B3→B20）に書式を設定した場合も、あるいは上のコードを走らせたあとでも、ほかのセルに設定された条件は
=$A2-$B2<>0
となっています（B20セルを選択して条件付き書式の画面で確認）。

で、B20のセルを、入力で100とすると色がつかず、100ではない数字を入れると赤く色がつきます。

なぜ式は
=$A2-$B2<>0
なのに、ほかの行（20行目など）で効果が出るのかが、わからないのです。

逆にいえば、20行目に
=$A20-$B20<>0
が自動的に挿入されているのなら、理解できる、という意味です。

結局Excelが、最初の行の条件式を参照して、自動的に$がついていない行方向に適用しているという仕様なのでしょうか。

回答No.1

f272

Range("B2:B20").Select
というようにB2:B20を選択して，その選択した範囲に
.FormatConditions.Add
で条件付き書式を追加していますよ。2行目で行っているだけではありません。
条件付き書式の書き方が，選択した範囲の先頭のセルを基準にした書き方になっているだけです。

補足

手動で（Excelのリボンから）条件付き書式を設定しても同じなのですが、条件というか判定式に
=$A2-$B2<>0
と設定しており、比較しているのはA列もB列も2行目だけを記述しています。

条件付き書式の設定する範囲は20行目までも理解しています。

しかしB2セルに条件付き書式設定後オートフィルで20行目まで（B3→B20）に書式を設定した場合も、あるいは上のコードを走らせたあとでも、ほかのセルに設定された条件は
=$A2-$B2<>0
となっています（B20セルを選択して条件付き書式の画面で確認）。

で、B20のセルを、入力で100とすると色がつかず、100ではない数字を入れると赤く色がつきます。

なぜ式は
=$A2-$B2<>0
なのに、ほかの行（20行目など）で効果が出るのかが、わからないのです。

逆にいえば、20行目に
=$A20-$B20<>0
が自動的に挿入されているのなら、理解できる、という意味です。

結局Excelが、最初の行の条件式を参照して、自動的に$がついていない行方向に適用しているという仕様なのでしょうか。