[[20180802171142]]　『新旧2つのシートを比較して、旧シートから変更さａx（7cats）　ページの最後に飛ぶ

『新旧2つのシートを比較して、旧シートから変更されたセルに色をつけたい』（7cats）

初めて質問させていただきます。
弊社で取り扱っている商品の価格表を毎月メンテナンスしています。

前月用の価格表に対して、
価格の改定によるD列、E列の値の書き換えや
顧客や商品の追加、削除による行追加、行削除などをおこない、
当月用の価格表を完成させています。

価格表の項目は、顧客名、商品名、サービス、価格A、価格B、検索キーです。
検索キーというのは、A列～C列の文字列を単純につなげたものです。
（基幹システムから必要なデータを抽出する際に使用しています）

旧シートから変更したセルの色を、新シートの中でハイライトさせたいのですが、良い方法が思い浮かびません。アドバイスいただけましたら幸いです。

旧シート

	A列	B列	C列	　D列	E列	F列
１行目	顧客名　商品名	サービス　価格A	価格B	検索キー
２行目	AA	ああ	松	　100	50	AAああ松
３行目	AA	いい	竹	　120	80	AAいい竹
４行目	BB	ああ	梅	　90	30	BBああ梅

新シート

	A列	B列	C列	　D列	E列	F列
１行目	顧客名　商品名	サービス　価格A	価格B	検索キー
２行目	AA	ああ	松	　100	50	AAああ松
３行目	AA	いい	竹	　"110"	80	AAいい竹
４行目	"AA"	"うう"	"竹"	　"90"	"50"	"AAうう竹"
５行目	"BB"	"いい"	"梅"	　"70"	"60"	"BBいい梅"
※"　"で囲んだセルをハイライトさせたい。

VLOOKUPで、新旧シートのF列の検索キーから、新旧2つのシートの各項目を比較して、項目ごとの値が変化したセルを見つける、という方法は考えましたが、
作業用のフォーマットを用意しないといけなさそうなのと、
変更箇所をハイライトさせる手段が手作業になってしまいそうなので、躊躇しています。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

G列を作業列とし、D列～F列を連結し
COUNTIF関数で、旧シートに同じものが存在するか調べ
存在しない場合は、条件付き書式で色をつけてはどうでしょうか。

（マナ） 2018/08/02(木) 18:24

VBAでもよければ､､
このコードは列ごとで見ているので､新しいデータでも同一列に同じものが存在したら色はつきません｡

Sub Macro例()

    Dim 新 As Worksheet, 旧 As Worksheet, Fc As Range, i As Long, j As Long
    Set 新 = ThisWorkbook.Sheets("新シート")
    Set 旧 = ThisWorkbook.Sheets("旧シート")

    For j = 1 To 6
        For i = 2 To 新.Cells(Rows.Count, "A").End(xlUp).Row
            Set Fc = 旧.Columns(j).Find(新.Cells(i, j).Value)
            If Fc Is Nothing Then
                新.Cells(i, j).Interior.Color = 65535
            End If
        Next i
    Next j
End Sub

（TAKA） 2018/08/02(木) 18:37

マナさん
コメントをくださり、ありがとうございます。
新旧両方のシートで、比較対象の列を一旦作業用の別列で連結したうえで、
COUNTIFで両シートの連結セル同士を比較して、
結果がゼロ（同じ内容の連結セルが存在しない）ならば、
条件付き書式で連結セルに色を付けることができました。
COUNTIFを使うというのが目ウロコでした。

変更件数が少ない場合には、まずこの方法で、どの行が変更されたかを把握して、
どのセルが変更になったかは目チェックしていくという進め方がとれそうです。
ありがとうございました。

どのセルが変更になったかも自動的にチェックしたいので、これからTAKAさんが
ご教示くださったVBAにトライしてみます。
（7cats） 2018/08/03(金) 11:59

フィルタオプションを使ってみました

１）新データを全選択し背景を黄色
２）旧データを検索条件にしてフィルタオプション実行
３）抽出行の背景を塗りつぶしなし
４）フィルタ解除

手作業でも簡単ですが
マクロだとこんな感じ

 Option Explicit

 Sub test()
    Dim oldData As Range
    Dim newData As Range

    Set oldData = Sheets("Sheet1").Cells(1).CurrentRegion
    Set newData = Sheets("Sheet2").Cells(1).CurrentRegion

    With newData
        .Interior.Color = vbYellow
        .AdvancedFilter xlFilterInPlace, oldData
        .Interior.ColorIndex = xlNone
        .Parent.ShowAllData
    End With

 End Sub

（マナ） 2018/08/03(金) 20:41

 COUNTIFSなら作業列を使わなくても条件付き書式で出来ますよ。

 1つ目、新シートにあって、旧シートに無い時に、行全体をハイライト。
 AからF列全体を選択して、条件付き書式の数式に、
 =AND($F1<>"",COUNTIF(旧!$F:$F,$F1)=0)

 2つ目、F列が同じで、D・E列が違う場合に、変更のあった単一セルをハイライト。
 DからE列全体を選択して、条件付き書式の数式に、
 =AND($F1<>"",COUNTIFS(旧!$F$1:$F$1000,$F1,旧!D$1:D$1000,D1)=0)
 COUNTIFSで列全体選択は遅くなるので、想定される最大行数に制限した方が良いです。

（sy） 2018/08/03(金) 21:55

TAKAさん
コメントくださり、ありがとうございます。
返信が遅れまして申し訳ありません。

いただいたコードを実行しましたところ、
意図しないセルがハイライトされました。
最初の私の説明が不足しており、大変申し訳ございません。

もう少し業務について説明させていただきます。

今回の価格表は、
「客先(Customer)」
「製品(Product)」
「製品に付属するサービス(Service)」
の組み合わせごとに価格が決まります。

価格には通常価格(PriceA)と特価（PriceB)の２種類があります。

当月の価格表のCustomer列とProduct列とService列を連結したKey列（F列）から
前月の価格表の同じくKey列（F列）を検索して一致するセルが存在する場合に、
当月価格表と前月価格表のPriceAを比較し、異なっていればハイライトさせる
当月価格表と前月価格表のPriceBを比較し、異なっていればハイライトさせる

ということをおこないたいのです。

マナさん　syさんのアドバイスも試してみます。
ありがとうございます。
（7cats） 2018/08/06(月) 13:22

syさん

アドバイスいただいた条件付き書式の設定で、期待したアウトプットを得ることができました！ありがとうございます。

マナさんの最初のアドバイスもそうですが、COUNTIF(S)を使って、条件一致するセルの有無を調べるという発想がなかったのと、条件付き書式の機能を今までほとんど使用したことがなかったので、気づきの多いアドバイスをたくさんいただくことができ、感謝しております。ありがとうございました。
（7cats） 2018/08/07(火) 16:55

コメント返信：

[ 一覧(最新更新順) ]

[[20180802171142]] 『新旧2つのシートを比較して、旧シートから変更さａx（7cats） ページの最後に飛ぶ

[[20180802171142]]　『新旧2つのシートを比較して、旧シートから変更さａx（7cats）　ページの最後に飛ぶ