データ検索＆セルの背景色変更

質問

ユーザーフォームを利用し、データの検索結果をユーザーフォームへ表示させ
一部の情報を変更・上書きした上で、その元データのセル背景色を変更したいのですが・・・どうもうまくできません。
ご助力お願いします。

≪詳細≫
シートＡは、データシートとしての表となっている。（商品データ）
シートＢは、廃棄商品のデータシート。（廃棄商品）

登録番号　　商品名　　型番　　状況
11111　　　りんご　　＠＠＠　　在庫
22222　　　みかん　　！！！　　在庫
33333　　　いちご　　＃＃＃　　在庫

検索ボタン　　　更新ボタン

※ユーザーフォームで、登録番号を入力+検索ボタンクリックすると、テキストボックス（ユーザーフォーム上）に登録番号・商品名・型番・状況が表示されるようになっている。
※更新ボタンを押すと、廃棄商品シートへ入力される。

検索・表示後、状況を「在庫→廃棄」に変更し上書きする。
※上書き・・・データのあった列・行に上書きする。
例　2列目にあるみかんの検索の場合、
　　「22222」を入力・検索し、ユーザーフォーム上に
　　「22222」「みかん」「！！！」「在庫」を表示させる。
　　「在庫」のみを「廃棄」に変更し、2列目の状況のみを変え
　　上書きする。

上書き後、この2列目の「22222」「みかん」「！！！」「廃棄」の
セル背景色をグレーに変更する。

ちなみに上書き＆背景色変更は、更新ボタンのワンクリックにて
動作させたいのですが、検索したデータを指定するコードがわかりません。
宜しくお願いします。
まだまだＶＢＡ初級者のため、具体的に教えて頂けると有難いです。

Private Sub cmd検索_Click()
Dim SerchKey As String
Dim SerchArea As Range
Dim lRow As Long

lRow = Sheets("商品データ").Cells(65536, "A").End(xlUp).Row + 1
SearchKey = Application.InputBox( _
Prompt:="登録番号を入力して下さい。", Type:=2)
If SearchKey = "" Or SearchKey = "False" Then
Exit Sub
End If

Set SearchArea = Sheets("商品データ").Range(Sheets("商品データ").Range("A1"), Sheets("商品データ").Range("A1").End(xlDown))

Set FoundCell = SearchArea.Find( _
What:=SearchKey, _
SearchOrder:=xlByRows, _
LookAt:=xlWhole, _
MatchCase:=False)

If FoundCell Is Nothing Then
MsgBox "見つかりません", vbCritical
GoTo ExitHandler
End If

With FoundCell
商品登録.txt登録番号.Value = .Value
商品登録.txt商品名.Value = .Offset(0, 1).Value
商品登録.txt型番.Value = .Offset(0, 2).Value
商品登録.txt状況.Value = .Offset(0, 3).Value

商品登録.txt状況.SetFocus

End With
ExitHandler:
Set SearchArea = Nothing
Exit Sub
End Sub

Private Sub cmd更新_Click()
Dim lRow As Long
lRow = Sheets("廃棄商品").Cells(65536, "A").End(xlUp).Row + 1

With Sheets("廃棄商品")
.Cells(lRow, "B").Value = 商品登録.txt登録番号.Value
.Cells(lRow, "A").Value = 商品登録.txt商品名.Value
.Cells(lRow, "C").Value = 商品登録.txt型番.Value
.Cells(lRow, "D").Value = 商品登録.txt状況.Value
　　End With

MsgBox "廃棄商品シートへ登録しました。", vbInformation
End Sub

回答No.2

rukuku · ベストアンサー

はじめまして

だいたいの状況は分かりますが、詳細までは正確に分からないので「考え方」のアドバイスをします。

＞検索したデータを指定するコードがわかりません
検索したデータがあるセルは「FoundCell」にあります。

fumfumponpさんのプログラムの
With FoundCell
…
End With
の部分がサンプルプログラムですよ！

問題になる点があるとすれば、「検索」と「更新」のプログラム（プロシージャ）の間で「FoundCell」のデータをやりとりする方法だと思います。
その方法としては以下の２つが思い浮かびました。
１．「DIM」と使って変数を定義するときに、モジュール内では共通に使えるように指定する。
　（Sub … End Sub）の外に
　Dim FoundCell
　を記述する
　参考：http://www.k1simplify.com/vba/tipsleaf/leaf330.html
２．隠しＢＯＸにセルのアドレスを記載する
　テキストボックスやラベルには「visible」というプロパティがあって、これを「False」とすると、実際には表示されません。
　表示されないだけで、実際には存在するので「.value」を使えば内容を読み込むことができます。

文書だけなので、言葉足らずのことはあると思います。
分からない点がありましたら回答を締め切らず、「この回答への補足」に書いてください。

p(^^)q

回答No.1

rukuku

はじめまして

だいたいの状況は分かりますが、詳細までは正確に分からないので考え方のアドバイスをします。

＞検索したデータを指定するコードがわかりません
検索したデータがあるセルは「FoundCell」にあります。

fumfumponpさんのプログラムの
With FoundCell
…
End With
の部分がサンプルプログラムですよ！

問題になる点があるとすれば、「検索」と「更新」のプログラムの間で「FoundCell」のデータをやりとりする方法だと思います。
その方法としては以下の２つが思い浮かびました。
１．「DIM」と使って変数を定義するときに、モジュール内では共通に使えるように指定する。
　（Sub … End Sub）の外に
　Dim FoundCell
　を記述する
　参考：http://www.k1simplify.com/vba/tipsleaf/leaf330.html
２．隠しＢＯＸにセルのアドレスを記載する
　テキストボックスやラベルには「visible」というプロパティがあって、これを「False」とすると、実際には表示されません。
　表示されないだけで、実際には存在するので「.value」を使えば内容を読み込むことができます。

文書だけなので、言葉足らずのことはあると思います。
分からない点がありましたら回答を締め切らず、「この回答への補足」に書いてください。

p(^^)q

お礼

rukukuさんありがとうございます。

(1)の方法でやったらできました。
言われるとその通りで、基本的なことを
見落としてました。

ありがとうございます。

>１．「DIM」と使って変数を定義するときに、モジュール内では共通に使えるように指定する。
　（Sub … End Sub）の外に
　Dim FoundCell
　を記述する