エクセル 条件式書式 数式

質問

仮にG2からG4セルの数値が　G8からG14までの数値と同じなら色を付けるという数式
=SUM(IF($G$2:$G$4=G8,1,0))>0　　で
このデーターが２００ほどありまして
エクセル　の「開発」でのマクロ　でどんどんやりたいのですが
書式設定し行の挿入をして　１行横えずらすたびに　　違う数値に色がつきます
=SUM(IF(G$2:G$4=G8,1,0))>0
=SUM(IF($G2:$G4=G8,1,0))>0
=SUM(IF(G2:G4=G8,1,0))>0　　の３パターンどれもだめです
どうすればいいでしょうか

回答No.7

tom04 · ベストアンサー

No.3・4です。

補足をよくよく読んでみると
結局条件付き書式の数式が絶対参照になっている部分を複合参照にすれば良いのではないでしょうか？
数式を
=SUM(IF(A$3:A$12=A15,1,0))>0
のように$マークを行だけ固定の「複合参照」にしてみては？
これでA列の3～12行目を参照するのではなく、列挿入すると参照先はそのまま移動するはずです。

=COUNTIF(A$3:A$12,A15)
という数式でも同じ結果になるとは思いますが・・・

それから列挿入　→　A列にデータ貼り付け　という操作を200回繰り返す！というコトですが、
もちろんVBAで可能です。
ただ、質問文を見る限りどういったデータをどこから引っ張ってきて貼り付けるのか？が明記されていないので
コードの書きようがありません。

とりあえずは「条件付き書式」の対処だけです。m(_ _)m

お礼

出来ました。有り難うございました。助かりました
=SUM(IF(A$3:A$12=A15,1,0))>0の式を
次郎の数値１つ１つに設定すればよかったんですねー
それを次郎の数値をすべて選択して一番最初の数値に設定していました。それがよくなかったんでしょうか。
なんども付き合っていただいてたすかりました。

補足

なんどもすいませんね
＞=SUM(IF(A$3:A$12=A15,1,0))>0
のように$マークを行だけ固定の「複合参照」にしてみては？＜
一回一回手動というか　できます　が
困っているのは　この手順を　エクセルの開発　マクロ？　に覚えさせてもうまくいきません。　太郎のA3のセルの数値の色だけ（つまり太郎の頭の数値）を次郎の数値につけ　又、それより前の分はすべて色が消えます

なんでなんでしょうか

回答No.6

kkkkkm

条件付き書式を設定しているのは$A$15:$A$25で、その条件が=SUM(IF($A$3:$A$12=A15,1,0))>0なのですよね。示されたデータの例だとA18からA21までが指定された色が付きます。

ちなみに、例示されたデータはA列もB列も同じデータですので同じデータで同じ個所に色がつかないと条件付き書式が有効じゃないことになりますが…。

> 次にA列を次郎のさいごまでコピーしてB列に貼り付けます

これで、A列をB列にコピーすればA列とB列のデータは同じですし、条件式の範囲指定が「行列とも絶対参照」になっているわけですから、当然A列のデータを変化させたとしてもB列の条件付き書式の設定がされた$B$15:$B$25の同じ行に色がつくのは当たり前です。ですので、前回の回答で=SUM(IF(A$3:A$12=A15,1,0))>0という「行だけ絶対参照」にする式にすることによって、それぞれの列の3行目から12行目までのデータによって色がつく行が変化しますけどと申し上げたのですが。

> B列に１つ列を挿入

何をしたいのか意味が分かりません。

200回繰り返したいのでしたら

For i=1 to 200

したいことはマクロの記録でコードを取得してここに記載

Next

> 移動させれば　色をつけた箇所が毎回　A列の太郎の１０個の数値の色を返してきます。

コピーとか移動とか語句が一定しませんが、何をどう移動するのでしょう?

ちなみに、どのセルを条件付き書式の対象として範囲指定していますか、例示されたデータだとA15からA25の範囲指定になりますが、そうなっていますか?

お礼

出来ました。有り難うございました。
=SUM(IF(A$3:A$12=A15,1,0))>0の式を
次郎の数値１つ１つに設定すればよかったようです。
それを次郎の数値をすべて選択して一番最初の数値に設定していました。それがよくなかったんでしょうか。
なんども付き合っていただいてすいませんでした。

回答No.5

kkkkkm

2013ですが、元の式を

=SUM(IF(G$2:G$4=G8,1,0))>0

にして、条件付きの部分だけをK列に手動でコピーしたら

=SUM(IF(K$2:K$4=K8,1,0))>0

に変更されて適用先も
=$K$8:$K$14
になりその後列の挿入をしても列の値がその分変更されて(一列挿入したらL列に変更されました)質問には行の挿入とありますが行じゃなく列ですよね。1行横にずらすというのはあり得ませんから、列だと判断しました。

> もとの行のG2ーG4の数値を変えると　
> 移動した列のG８ーG１４の条件式も影響を受けます

ということはなく、コピー先のK列の2行目から4行目のデータで$K$8:$K$14に条件付き書式が反映されました。マクロに何か問題があるのではないでしょうか。もしくは、最初の条件付き書式の範囲がG8からG14以上のところにまで及んでいるということはありませんか。マクロ実行前と実行後の条件付き書式がどのようになっているのか確認されたらいかがでしょう。

補足

すいませんはじめからやります。
まずA列の太郎に毎回違うデーターを貼り付けます
この場合仮に１０個の数値です。
その下、次郎にも毎回違うデーターを貼り付けます
そして太郎の数値と同じものが次郎にあれば次郎の方の数値に色を付ける　書式は=SUM(IF($A$3:$A$12=A15,1,0))>0です
次にA列を次郎のさいごまでコピーしてB列に貼り付けます
B列に１つ列を挿入　この作業をくり返して２００回ほどしなければらりません。ですのでエクセルの開発のマクロ？で
ボタンを押せばこれをくり返えせるとおもったのですが
移動させれば　色をつけた箇所が毎回　A列の太郎の１０個の数値の色を返してきます。影響を受けない条件式の書式がしりたいのです。またこれらができるVBAのコードがあればお願いしたいのです、厚かましいことですいません。宜しくお願いします。

A列 　　　　　　　　　B列
太郎 　　　　　　　　太郎２
1 　　　　　　　　　1
2 　　　　　　　　　2
3 　　　　　　　　　3
4 　　　　　　　　　4
5 　　　　　　　　　5
6 　　　　　　　　　6
7 　　　　　　　　　7
8 　　　　　　　　　8
9 　　　　　　　　　9
10 　　　　　　　　　10

次郎 　　　　　　　　次郎２
11 　　　　　　　　　11
12 　　　　　　　　　12
13 　　　　　　　　　13
8 　　　　　　　　　8
1 　　　　　　　　　1
3 　　　　　　　　　3
7 　　　　　　　　　7
15 　　　　　　　　　15
17 　　　　　　　　　17
20 　　　　　　　　　20
30 　　　　　　　　　30

回答No.4

tom04

No.3です。
たびたびごめんなさい。

＞このデーターが２００ほどありまして
すなわち結構の範囲が条件付き書式の対象となる訳ですかね？
そうであれば一つ一つループさせる方法ではなく、条件付き書式を設定する方法にしてみました。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
コピー＆ペースト　→　Excel画面にデータ入力・行挿入・行削除等の操作をしてみてください。

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から
With Cells
.FormatConditions.Delete
.Interior.ColorIndex = xlNone
End With
With Range("G8:G14") '←ココで対象セルを選択
.FormatConditions.Add xlExpression, Formula1:="=COUNTIF($G$2:$G$4,G8)" '←数式は条件付き書式の数式
.FormatConditions(1).Interior.ColorIndex = 3 '←「赤」に設定
End With
End Sub 'この行まで

※　今回は条件付き書式を設定するようにしていますので、
条件付き書式のクリアは無視しても大丈夫です。
※　実際のデータ範囲はコード内にコメントを入れていますので、
そこで変更してみてください。m(_ _)m

補足

すいませんはじめからやります。
まずA列の太郎に毎回違うデーターを貼り付けます
この場合仮に１０個の数値です。
その下、次郎にも毎回違うデーターを貼り付けます
そして太郎の数値と同じものが次郎にあれば次郎の方の数値に色を付ける　書式は=SUM(IF($A$3:$A$12=A15,1,0))>0です
次にA列を次郎のさいごまでコピーしてB列に貼り付けます
B列に１つ列を挿入　この作業をくり返して２００回ほどしなければらりません。ですのでエクセルの開発のマクロ？で
ボタンを押せばこれをくり返えせるとおもったのですが
移動させれば　色をつけた箇所が毎回　A列の太郎の１０個の数値の色を返してきます。影響を受けない条件式の書式がしりたいのです。またこれらができるVBAのコードがあればお願いしたいのです、厚かましいことですいません。宜しくお願いします。

A列 　　　　　　　　　B列
太郎 　　　　　　　　太郎２
1 　　　　　　　　　1
2 　　　　　　　　　2
3 　　　　　　　　　3
4 　　　　　　　　　4
5 　　　　　　　　　5
6 　　　　　　　　　6
7 　　　　　　　　　7
8 　　　　　　　　　8
9 　　　　　　　　　9
10 　　　　　　　　　10

次郎 　　　　　　　　次郎２
11 　　　　　　　　　11
12 　　　　　　　　　12
13 　　　　　　　　　13
8 　　　　　　　　　8
1 　　　　　　　　　1
3 　　　　　　　　　3
7 　　　　　　　　　7
15 　　　　　　　　　15
17 　　　　　　　　　17
20 　　　　　　　　　20
30 　　　　　　　　　30

回答No.3

tom04

こんばんは！
質問の内容は行挿入（列挿入）しても条件付き書式の設定範囲は常にG8～G14の範囲で固定したい！
というコトでしょうか？
そうであれば、行挿入（列挿入）した場合は挿入されたセルも適応され、条件付き書式の範囲も
挿入された行数（列数）分だけ広がってしまいますね。

そこで条件付き書式ではなく、VBAでの一例です。
条件付き書式の設定はセルの塗りつぶしを「赤」にするという前提でのコードです。
↓のコードを操作したいシートモジュールにコピー＆ペーストしてデータ変更・行挿入等を行ってみてください。

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から
Dim c As Range, r As Range, myRng As Range
Set myRng = Range("G2:G4")
Cells.Interior.ColorIndex = xlNone
For Each c In Range("G8:G14")
Set r = myRng.Find(what:=c, LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then
c.Interior.ColorIndex = 3 '←「赤」としています
End If
Next c
End Sub 'この行まで

※　対象セルに条件付き書式の設定があると反応しませんので、
条件付き書式のルールはクリアしておいてください。

※　本題から離れますがお示しの条件付き書式の数式は
=COUNTIF($G$2:$G$4,G8)
というコトだと判断しています。m(_ _)m

回答No.2

kkkkkm

=SUM(IF($G$2:$G$4=$G8,1,0))>0

ではだめでしょうか。

補足

仮にG2からG4セルの数値が
G8からG14までの数値と同じならG8からG14のほうに色を付ける
=SUM(IF($G$2:$G$4=G8,1,0))>0　で色はつきますが
この列をコピーして移動させて　次のデーター作業で
もとの行のG2ーG4の数値を変えると　
移動した列のG８ーG１４の条件式も影響を受けます
そうならないように　です　言葉足らずですいません

回答No.1

asuncion

＞仮にG2からG4セルの数値が
＞G8からG14までの数値と同じなら色を付けるという数式
＞=SUM(IF($G$2:$G$4=G8,1,0))>0　　で

G9～G14はどこへ行っていますか？

補足

仮にG2からG4セルの数値が
G8からG14までの数値と同じならG8からG14のほうに色を付ける
=SUM(IF($G$2:$G$4=G8,1,0))>0　で色はつきますが
この列をコピーして移動させて　次のデーター作業で
もとの行のG2ーG4の数値を変えると　
移動した列のG８ーG１４の条件式も影響を受けます
そうならないように　です　言葉足らずですいません