VBA Excel 背景色の最終行取得～

質問

Excelのシート上に背景色が変更になった箇所のH3から
背景色の最終行を取得し
その最終行のB列の値を取得したいのですが宜しくお願いします。
取得した値をMsgBoxで表示できる様に作成したいのですが、
背景色の最終行って、どの様に作成したら良いのでしょうか？
UserForm上で操作ができたら良いのですが。
画像の様な事をしたいです。
お手数おかけしますが教えて頂けますでしょうか？

この投稿のマルチメディアは削除されているためご覧いただけません。

回答No.2

kkkkkm · ベストアンサー

Sub Example()
Dim c As Range

For Each c In Range("H3:H1000")
If c.Interior.Pattern = xlNone Then
MsgBox Cells(c.Row - 1, "B").Value
Exit For
End If
Next
End Sub

みたいな感じでいかがでしょう。

補足

回答ありがとうございます。
UserForm上で実行して見ましたが、シート名が無かったのを気づいて付け足してみたんですが、こんな感じでも大丈夫でしょうか？
Dim c As Range

With Worksheets("Sheet1")
.Activate
For Each c In Range("H3:H1000")
If c.Interior.Pattern = xlNone Then
MsgBox = Cells(c.Row - 1, "B").Value
Exit For
End If
Next
End With

一応、動作はするんですが・・・

回答No.4

kkkkkm

> With～End Withに限って、ドットを入れといた方が良いんですよね？

はい、普通の時にドットを入れるとエラーになります。
With Worksheets("Sheet1")
というのは、その記述以降End Withまでは
たとえば通常
Worksheets("Sheet1").Range("A1").Value
と書く部分の
Worksheets("Sheet1").
を省略してドットから以降だけ書けばいいですよという意味と考えてください。

With Worksheets("Sheet1").Range("A1")
と書くこともできます。セルA1の書式設定なんかを一括処理したい場合は便利だと思います。

ただ、ドットがなくてもエラーにはならず別のセルを参照してしまいますから、不思議な結果が出たりするので利用には注意が必要です。

先の回答の誤動作のテストといて
元のコードの
.Activate
のあとに
Sheets("Sheet2").Activate
を入力してみてください、するとRangeなどにドットを入れていない場合正しく動作しないと思います。

ただし、今回のようなフォーム内のモジュールではなくSheetモジュールの場合は、単にRangeと指定した場合はそのモジュールの存在するSheetのセルを参照するのでSheet1のモジュールでしたら正しく動作します。

お礼

返事ありがとうございます。
詳しく説明して頂きまして本当に助かりました。
おかげで充分理解する事ができました。
また、何かありました時には宜しくお願いします。

回答No.3

kkkkkm

> UserForm上で実行して見ましたが、シート名が無かったのを気づいて付け足してみたんですが、こんな感じでも大丈夫でしょうか？

すみません、シート名に関しての説明を忘れてました。

With Worksheets("Sheet1")
.Activate

とされてますので、不要かもしれませんが、後に機能追加でコードに手を加えた時に.Activateのあとで他のシートをアクティブにするコードを入れてしまった場合、誤動作の原因になりますので、念のためにRangとCells前にドットを入れておく方が安全かもしれません。
.Range("H3:H1000")
.Cells(c.Row - 1, "B").Value

ところで

MsgBox = Cells(c.Row - 1, "B").Value

これ、タイプミスですよね。

補足

回答ありがとうございます。
MsgBox Cells(c.Row - 1, "B").Value
すいませんタイプミスでした。

RangeやCellsの前にドットを入れると、その様な効果があるんですか？知りませんでした。
With～End Withに限って、ドットを入れといた方が良いんですよね？

回答No.1

watabe007

>H3から背景色の最終行を取得
Sub Test()
　　Dim myRng As Range
　　Application.FindFormat.Interior.ColorIndex = Range("H3").Interior.ColorIndex
　　Set myRng = Columns("H:H").Find(What:="", After:=Range("H1"), SearchFormat:=True, SearchDirection:=xlPrevious)
　　MsgBox Cells(myRng.Row, "B").Value
End Sub

Sub Test2()
　　Dim i As Long
　　i = 4
　　Do
　　　　If Range("H3").Interior.ColorIndex <> Cells(i, "H").Interior.ColorIndex Then Exit Do
　　　　i = i + 1
　　Loop
　　MsgBox Cells(i - 1, "B").Value
End Sub

お礼

回答ありがとうございます。
希望通りの動作ができました。
UserFormからの操作だとSheet名を指定したら動作ができました。
一応、希望通りの動作できましたし
しかも、2つのコードを記載して頂き、とっても参考になりました。

補足

今、気が付きました！
Test1の方がCtrl+↑見たいな感じで背景色を見ているんですね。
そして・・・
Test2の方がCtrl+↓見たいな感じで背景色を見ていることがわかりました。
上手く説明できませんが、参考になります。
本当にありがとうございます。