[[20210717090458]]　『「マクロ相対参照で記録」で、特定のセルに限定さ』（にこ）　ページの最後に飛ぶ

『「マクロ相対参照で記録」で、特定のセルに限定されないよう記録できない』（にこ）

毎日繰り返す作業を簡略化したいので、マクロを記録してみたのですが、相対参照を選択して記録しているにもかかわらず指定されるセルは固定されてしまいます。

・やりたいこと
選択する範囲が空白の場合、黄色に塗りつぶす。塗りつぶされたセルに入力したら色が消える
→条件付き書式のルールで、指定の値を含むセルだけを書式設定で「セルの値が空白」で設定しています

・記録したときに起こる問題
記録するときに、セルの位置はいじらずに、相対参照を選択して書式設定だけいじりました。
その時、たまたま選択していたセルがAC7だったのですが、記録したマクロを使うと「選択したセルが空白の場合」のセルがAC7に固定されてしまいます。
例えば空白のAC8を選択してマクロを使ったら、AC8は黄色くなりますが、AC8に入力しても背景色に変化はありません。しかし、AC7に文字を入力したらAC8が空白でも色が消えます。

以下、私が記録したマクロのコードです。

Sub Macro6()
'
' Macro6 Macro
'
' Keyboard Shortcut: Ctrl+a
'

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=LEN(TRIM(AC7))=0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

おそらく "=LEN(TRIM(AC7))=0"のAC7を何かしらの汎用性のある言葉に変えればいいのでしょうが、どう変えたらいいかわかりません。
私のやりたかったことは、先に範囲を選択しておいて、マクロを発動させたらその選択していた範囲にその書式設定が反映される、というものです。

よろしくお願いします。

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

例

 Sub Macro1マクロ記録()
    Range("A1:A10").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=A1="""""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
 End Sub

　↓

 Sub Macro1()
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" & Selection.Cells(1).Address(0, 0) & "="""""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = True
 End Sub
（ブヒ） 2021/07/17(土) 14:04

編集がかぶりましたがそのまま。

■１
仰ることを整理すると、

 (1)選択範囲に
 (2)自セルが空白（つまり＝""）だったら、黄色にするという条件付き書式を設定する

↑がゴールということですよね。

得られたマクロの記録から次のことがわかるとおもいます。

 ・選択範囲を表すには「Selection」を使う
 ・条件付き書式を設定（追加）するには「FormatConditions.Add」を使う
 ・「数式を使用して、書式設定するセルを決定」するには「Type」を「xlExpression」にして
   「Formula1」で数式を設定する

■２
また、普通の操作で確認できますが、"複数のセル"に数式をつかった条件付き書式を設定する場合、一番左上のセルに対する数式を記載するルールになっています。
では、選択した範囲（Selection）の"左上"はVBA的にどうやって表現するかというと、選択範囲の1行目、１列目ということになります。
したがって↓ですね

 Selection.Cells(1,1)

ここから、セル番地（文字列）を取り出すには、Addressプロパティを使います。
つまり↓ですね。

 Selection.Cells(1,1).Address

ここで、Addressプロパティについて調べてみてください
https://www.moug.net/tech/exvba/0050094.html
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.address

引数がいくつかありますが、今回注目すべきは「RowAbsolute」「ColumnAbsolute」です。
どちらも、規定値は「True」であり、Trueの場合絶対参照になると書いてあります。

今回は、"相対参照"にしたいのですから、Trueではまずいですよね。
したがって、↓のように記述すれば、選択範囲の左上のセル番地を相対参照で取り出せるということになります。

 Selection.Cells(1, 1).Address(RowAbsolute:=False, ColumnAbsolute:=False)

なお、Excelの世界では、論理値として考えたときに0はFalse、0以外はTrueとして扱うというルールがあることと、順番通りであれば引数名を記述する必要はないので、↓のようにしてもOKです。
（実際、結構見かけます）

 Selection.Cells(1, 1).Address(0, 0)

■３
ということを踏まえると、目標を達成するには

 (1)選択した範囲の条件付き書式をいったんクリアする（前のが残ってると困るので）
 (2)選択した範囲の左上セルを取得して、そのセルが空白（つまり＝""）だったらTrueになる数式を条件にした条件付き書式を設定する
 (3)(2)で設定した条件付き書式に黄色塗りつぶしという書式を設定する

となります。コードにするとこんな感じです。

    Sub さんぷる()
        Selection.FormatConditions.Delete
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=" & Selection.Cells(1, 1).Address(0, 0) & "="""""
        Selection.FormatConditions(1).Interior.Color = 65535
    End Sub

（もこな２） 2021/07/17(土) 14:18

お返事が遅くなり申し訳ありません。
お二方とも、ありがとうございました。どういうものなのかが大体理解できたので、これをもとにしばらくいじってみようと思います。
（にこ） 2021/07/19(月) 21:36

コメント返信：

[ 一覧(最新更新順) ]

[[20210717090458]] 『「マクロ相対参照で記録」で、特定のセルに限定さ』（にこ） ページの最後に飛ぶ

[[20210717090458]]　『「マクロ相対参照で記録」で、特定のセルに限定さ』（にこ）　ページの最後に飛ぶ