[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『シートで比較して違うセルに色を付けたい』（いな）
シート1とシート2の内容を比べて違っている場合にシート1のセルの色が変わるようにしたい。
シート1
B列 得意先名
C列 納入先名
D列 商品名
E列 商品名2
F列 数量
G列 メーカー名
H列 商品コード
I列 受注??
J列 受注日付
全て7行目から入力されています。
シート2
B列 得意先名
C列 納入先名
D列 商品名
E列 商品名2
F列 数量
G列 メーカー名
H列 商品コード
I列 受注??
J列 受注日付
全て2行目から入力されています。
シート2の内容がシート1に反映されるよう、既にマクロが組まれています。
このエクセルを客先に送り、シート1のみ変更が加えられる可能性があります。
※行が追加される場合があります。
この2つのシートを比較して、違った場合にシート1の方のセルの色を変えたいです。
どのようにマクロを組めばよろしいでしょうか？
教えて頂けると嬉しいです。
よろしくお願いします。
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
1 行が追加された場合は、その行は色が変わらないようにしたいです。
シート1に行が追加されても、比較するセルがずれないようにしたいです。
2 INDIRECT関数を使用し、条件付き書式でもやってみましたが、うまくいきませんでした。
3 下記途中まで書きましたが、行を追加する＋シート2のB2行目からという部分が組み込めません。
Sub シートを比較して違ったら色を変える()
Dim RETSU_S, RETSU_E, GYOU_S, GYOU_E As Long
RETSU_S = 1 '列をAから
RETSU_E = 26 '列をZまで
GYOU_S = 7 '行を7から
GYOU_E = 101 '行を101まで
Dim s1, s2 As Worksheet 'Worksheetsオブジェクト用
Set s1 = Worksheets("Sheet2") '比較元シート名
Set s2 = Worksheets("Sheet1") '比較先シート名
Dim retsu, gyou As Long 'この変数で列と行を指定する
For gyou = GYOU_S To GYOU_E '開始行から終了行まで
For retsu = RETSU_S To RETSU_E '開始列から終了列まで
If s1.Cells(gyou, retsu).Value <> s2.Cells(gyou, retsu).Value Then
'同じ位置のセルの値が等しくなければ、そのセルを赤で塗りつぶす。
s1.Cells(gyou, retsu).Interior.Color = RGB(255, 0, 0)
s2.Cells(gyou, retsu).Interior.Color = RGB(255, 0, 0)
End If
Next
Next
'
End Sub
エクセル初心者のため、お手数をお掛けしますがよろしくお願い致します。
（いな） 2022/04/21(木) 16:04
追加された行情報が必須になると思います。
ですので、シート2からシート1に転記する際に1番最後の列とかに隠し列等を用いて区別させるなどするのはいかがでしょうか
＞行を追加する＋シート2のB2行目からという部分が組み込めません。
行を追加する処理を組み込む必要はあるのでしょうか？
シート2のB2ってすでに分かっているので、組み込むも何もないとおもいますが、どういう意味ですか？
とりあえず、行追加については置いといて比較処理は下記のようにしてできるのではないでしょうか
Sub テスト()
Dim s1, s2 As Worksheet 'Worksheetsオブジェクト用
Set s1 = Worksheets("Sheet1") '比較元シート名
Set s2 = Worksheets("Sheet2") '比較先シート名
Dim arr1 As Variant, arr2 As Variant
arr1 = s1.Range("B7:J100").Value
arr2 = s2.Range("B2:J95").Value
For i = 1 To UBound(arr1, 1)
For j = 1 To UBound(arr1, 2)
If arr1(i, j) <> arr2(i, j) Then
'塗りつぶし処理
s1.Cells(i + 6, j).Interior.Color = RGB(255, 0, 0)
End If
Next
Next
End Sub
（aho） 2022/04/21(木) 17:06
なお蛇足ですが、
Dim s1, s2 As Worksheet
は、お作法としては
Dim s1 As Worksheet, s2 As Worksheet
としておいたほうがよろしいかと思います。
（じゃふ） 2022/04/22(金) 10:01
ありがとうございます。
無事にできました。
しかし「シート1」に行を追加すると、やはり指定セルがずれてしまいます。
行を挿入してもずれないようにする方法はありますでしょうか？
（いな） 2022/04/22(金) 10:42
>行を挿入してもずれないようにする方法はありますでしょうか？
「セル範囲」という観点で考えると、無理なんじゃないでしょうか？
「１行が１件のデータ」と考えると、
注文番号とかで管理できるかと。
（まっつわん） 2022/04/22(金) 10:51
昨日提示した「転記する際に1番最後の列とかに隠し列等を用いて区別させる」方法を用いて
コードを改変したところ、意図した処理ができたことをこちらでは確認しました。
（aho） 2022/04/22(金) 11:35
＞どのようにすれば解消しますでしょうか？
に対しては回答しておいて何ですが、
わたしなら、そもそもこの案件は条件付書式で対応すると思います。
理由は、
＞シート2の内容がシート1に反映されるよう、既にマクロが組まれています。
このマクロとの全容が不明である以上、どのように干渉するかわかったもんじゃないからです。
（じゃふ） 2022/04/22(金) 11:45
アドバイスありがとうございます。
毎回同じシートを使用するわけではないので、条件付き書式だとデータが上がってくる度に同じ処理が必要になるため、
使用できませんでした。
（いな） 2022/04/22(金) 12:05
「転記する際に1番最後の列とかに隠し列等を用いて区別させる」の意味が理解出来ないのですが、どのような処理になりますでしょうか？
（いな） 2022/04/22(金) 12:07
ごめんなさい、（じゃふ） 2022/04/22(金) 11:45 は全て、質問者さんへの返答のつもりでした。
＞行を挿入してもずれないようにする方法はありますでしょうか？
これに対してですね。
ahoさんのお名前を出す以上、返信相手はわかりやすくしておくべきでした。
失礼いたしました。以後配慮いたします。
（じゃふ） 2022/04/22(金) 12:08
ahoさんへ
かき回しただけになってしまい、申し訳ありませんでした。
（じゃふ） 2022/04/22(金) 12:19
＞いなさん
最初に提示されていたコードで、セル同士の比較を行っていましたよね？
その考えを元にコードを組むならということで助言させていただいてます。
しかし、シート1は行の追加があるということで、追加行よりも下のセルの比較ではシート1とシート2の比較業の差異が出てくる。
なので、追加された行と追加されていない行を区別するようにと、アドバイスしました。
転記する際にK列(最終列がJ列のようなので)等に任意文字を追加すれば、対象行のK列の値の有無で
判別が可能になるのでは、ということが言いたかったのです。
・・・丁寧に説明いたしましたが、意図が理解できましたか？
（aho） 2022/04/22(金) 12:23
追加された行と追加されていない行を区別する→k列に文字を追加して、最終列として判別させるという事でしょうか？
（いな） 2022/04/22(金) 12:42
えーっと、たぶん、そういうことです。
（aho） 2022/04/22(金) 13:43
挿入した行にはk列の文字がない(挿入された行である事が判別できる）という事ですね？
K列に文字を入れる所まではできましたが、やはりコードが分からないので教えて頂きたいです。
（いな） 2022/04/22(金) 14:15
私には難しくまったく思い浮かばないので、もっと勉強してから出直してきます(^^♪
詳しく教えて頂きありがとうございました。
（いな） 2022/04/22(金) 16:10
＞私には難しくまったく思い浮かばないので、もっと勉強してから出直してきます(^^♪
もう閲覧されにこないかもですが...エクセル初心者の方がされるには 少々難しいかもですね。私も初心者ですが^^; 1案を
＞行を挿入してもずれないようにする方法はありますでしょうか？
If s1.Cells(gyou, retsu).Value <> s2.Cells(gyou, retsu).Value Then '同じ位置のセルの値が等しくなければ、そのセルを赤で塗りつぶす。 s1.Cells(gyou, retsu).Interior.Color = RGB(255, 0, 0) s2.Cells(gyou, retsu).Interior.Color = RGB(255, 0, 0) End If
上記のマクロの意図をするには、（まっつわん）さんがおっしゃるように 注文番号などの重複しない検索Keyが欲しいかなと思います。
なので、手始めにもっと簡単に… シート1（ 初期状態 ）において 変更点があったセルのみを、例えばフォントを赤に、背景色を薄い赤などに して特定できれば、より業務の効率化にはなると思います。
下記の表で説明するとですが、シート1は、客先に渡した初期状態です。 シート1にイベントを仕掛ければよいのではないでしょうか？ ※セルの値が変更時に処理を行う（Worksheet_Change）
■シート1（ 初期状態サンプル ）
|[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J]
[6]|得意先名|納入先名|商品名|商品名2|数量|メーカー名|商品コード|受注 |受注日付
[7]|B7 |C7 |D7 |E7 | 10|G7 |H001-0007 |受注1|2022/4/22
[8]|B8 |C8 |D8 |E8 | 20|G8 |H001-0008 |受注2|2022/4/23
[9]|B9 |C9 |D9 |E9 | 30|G9 |H001-0009 |受注3|2022/4/24
■シート1（ 客先戻り ）
|[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K]
[6] |得意先名|納入先名|商品名|商品名2|数量|メーカー名|商品コード|受注 |受注日付 |
[7] |B7 |C7 |D7 |E7 | 10|G7 |H001-0007 |受注1|2022/4/22|
[8] |B8 |C8 |D8 |E8 |(99)|G8 |H001-0008 |受注2|2022/4/23|
[9] | | | | | | | | | |insert-line
[10]|B9 |C9 |D9 |E9 | 30|G9 |H001-0009 |受注3|2022/4/24|
お客様がセルを変更する…フォントをバレないように、(ちょこっと薄い黒)にします。 例としてＦ8セル'(99)をこんなかんじに Font.Color = RGB(0, 0, 1) 見た目は絶対に人間の目では判断できません。…視力が3.0以上ある方は知りませんが..(笑)
＞「転記する際に1番最後の列とかに隠し列等を用いて区別させる」の意味が理解出来ないのですが、どのような処理になりますでしょうか？ ＞ K列に文字を入れる所まではできましたが、やはりコードが分からないので教えて頂きたいです。
ご自身でK列に文字を入れてどうするのｗ お客様が行を挿入されたら、VBAで判定して…何でもよいのでマークするのです。 上記の例は、"insert-line" になってますが"○"とか好きに変更されればよいです。 勿論、"insert-line"文字のフォントは...お客様には見えないようにします。
で最後に シート1（ 客先戻り ）に対して、Font.Color = RGB(0, 0, 1)と、"insert-line" をキーワードにして
Font.Color = RGB(0, 0, 1)ならば、お好みで .Font.Color = RGB(255, 0, 0) .Interior.Color = RGB(247, 234, 233) のようにして、K列も "insert-line" があれば なにもしないにすればよいですね。
（あみな） 2022/04/22(金) 19:13
（チンチクリン） 2022/04/22(金) 23:17
行の挿入というのが、単なる空行の挿入なら対応が可能でしょう。
両方から空行を除いたうえで比較すればよいので。
そうではなく、挿入してデータを追加しているとすると、
変更前から存在するものとの比較が必要となり、難しくなると思います。
また、今後に向けた対応と、すでにあるものの処理の話は分けて考えたほうがよいようです。
こちらの掲示板で、文字列の差分比較のコードを書いたことがあります。
LCS(Longest Common Subsequence:最長共通部分列)のアルゴリズムを使ったものです。
でも、Diff系のフリーソフトなどを探したほうがよいでしょう。
ネットで "VBA Diff テキスト"などで検索すると、関連記事が見つかります。例えば、
http://nonsoft.la.coocan.jp/Chinamini/20100001/20100218.html
などというフリーのツールを使ったテキストの差分表示ツールがありました。
VBAからでも使えるようですが、インストールからして、慣れないと難しいかもしれません。
（γ） 2022/04/23(土) 10:22
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.