[[20200825110401]]　『VBAで条件付き書式を設定』（やっち）　ページの最後に飛ぶ

『VBAで条件付き書式を設定』（やっち）

最終入力セルを認識し、そこから5マス下まで複数の条件付き書式を設定しようとコードを書いてみたのですが、解決できない点があったのでお力添えいただければと思います。

 Sub 入力()
 Dim n As Long
     n = Cells(Rows.count, "F").End(xlUp).Row 'nをF列の最終入力セルの行番号とする

 Cells.FormatConditions.Delete

    With Range(Cells(2, "A"), Cells(n + 5, "AE"))
        .FormatConditions.Add Type:=xlExpression, Operator:=xlEqual, Formula1:="=CELL(""row"")=ROW()"
        .FormatConditions(1).Interior.Pattern = xlGray50
        .FormatConditions(1).Interior.PatternColor = RGB(0, 0, 255)
    End With

    With Range(Cells(2, "C"), Cells(n + 5, "C"))
        .FormatConditions.Add Type:=xlExpression, Operator:=xlEqual, Formula1:="=COUNTIFS($G$" & CStr(2) & ":$G$" & n + 5 & ",$G$" & CStr(2) & ",$R$" & CStr(2) & ":$R$" & n + 5 & ",$R$" & CStr(2) & ")>1"
        .FormatConditions(2).Interior.Pattern = xlGray75
        .FormatConditions(2).Interior.PatternColor = RGB(255, 153, 255)
    End With

    With Range(Cells(2, "F"), Cells(n + 5, "F"))
        .FormatConditions.Add Type:=xlExpression, Operator:=xlEqual, Formula1:="=$AD" & CStr(2) & "=""○"""　'……１
        .FormatConditions(3).Font.Strikethrough = True '……２
    End With

    With Range(Cells(2, "G"), Cells(n + 5, "G"))
        .FormatConditions.Add Type:=xlExpression, Operator:=xlEqual, Formula1:="=$AE" & CStr(2) & "=""○"""
        .FormatConditions(4).Font.Strikethrough = True
    End With
 End Sub

１　=$AD2="○"を条件として入力したいけれど=$AD1048555="○"になってしまう。(CStr(2)では意味がない？)
２　１の条件に当てはまるとき取り消し線が引かれるようにしたいけれどインデックスが有効範囲にありませんとエラーを吐いてしまう。(１で範囲が1048555になっているのが悪さをしてる？)

拙いコードで申し訳ありませんが何とぞご指導よろしくお願いします。

＜使用 Excel：Excel2007、使用 OS：WindowsVista ＞

 =$AD1048555="○"は再現できなかったけど、
 インデックスのほうはWithでくくっちゃえば、インデックス指定する必要ないんじゃないですかね？
    Sub 入力()
       Dim n As Long
       n = Cells(Rows.Count, "F").End(xlUp).Row 'nをF列の最終入力セルの行番号とする
       Cells.FormatConditions.Delete
       With Range(Cells(2, "A"), Cells(n + 5, "AE")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=CELL(""row"")=ROW()")
           .Interior.Pattern = xlGray50
           .Interior.PatternColor = RGB(0, 0, 255)
       End With
       With Range(Cells(2, "C"), Cells(n + 5, "C")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=COUNTIFS($G$2:$G$" & n + 5 & ",$G$2,$R$2:$R$" & n + 5 & ",$R$2)>1")
           .Interior.Pattern = xlGray75
           .Interior.PatternColor = RGB(255, 153, 255)
       End With
       With Range(Cells(2, "F"), Cells(n + 5, "F")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=$AD2=""○""")   '……１
           .Font.Strikethrough = True '……２
       End With
       With Range(Cells(2, "G"), Cells(n + 5, "G")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=$AE2=""○""")
           .Font.Strikethrough = True
       End With
    End Sub

 ちなみに、インデックスエラーになる理由は、そのセルに条件付き書式が3つもセットされてなかったからだと思います。

（稲葉） 2020/08/25(火) 12:08

稲葉さん返信ありがとうございます。
返信を参考にコードを書きなおしたところ、１の問題点は解決され、インデックスエラーも回避できました。(シート全体に設定されている条件付き書式の個数をカウントしていると勘違いしていた)

しかし下記コードではn+5=2608の時、上から順に
　=CELL("row")=ROW()の時、　=$A$2:$E$2608,$H$2:$AE$2608　に網かけ+取り消し線
　=CELL("row")=ROW()の時、　=$A$2:$AE$2608　に網掛け＋取り消し線
　=CELL("row")=ROW()の時、　=$A$2:$AE$2608　に網掛けのみ★
　=COUNTIFS($G$2:$G$2608,$G$2,$R$2:$R$2608,$R$2)>1の時、　=$C$2:$C$2608に網掛けのみ★
　=$AD2="○"の時、　=$F$2:$F$2608に書式設定なし○
　=$AE2="○"の時、　=$G$2:$G$2608に書式設定なし○

の６つが登録されてしまいます。
★の条件付き書式２つはそのままに、○の条件付き書式の書式設定に取り消し線を入れ、印がないものは消去したいと考えています。
どうして設定していないつもりの条件付き書式が出てくるのか、なぜねらった条件に対して書式設定できていないか回答いただければと思います。
よろしくお願いします。

    Sub 入力()
       Dim n As Long
       n = Cells(Rows.count, "F").End(xlUp).Row 'nをF列の最終入力セルの行番号とする
       Cells.FormatConditions.Delete
       Call Range(Cells(2, "A"), Cells(n + 5, "AE")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=CELL(""row"")=ROW()")
            Range(Cells(2, "A"), Cells(n + 5, "AE")).FormatConditions(1).Interior.Pattern = xlGray50
            Range(Cells(2, "A"), Cells(n + 5, "AE")).FormatConditions(1).Interior.PatternColor = RGB(0, 0, 255)

       Call Range(Cells(2, "C"), Cells(n + 5, "C")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=COUNTIFS($G$2:$G$" & n + 5 & ",$G$2,$R$2:$R$" & n + 5 & ",$R$2)>1")
            Cells(2, "C").Select
            Range(Cells(2, "C"), Cells(n + 5, "C")).FormatConditions(2).Interior.Pattern = xlGray75
            Range(Cells(2, "C"), Cells(n + 5, "C")).FormatConditions(2).Interior.PatternColor = RGB(255, 153, 255)

       Call Range(Cells(2, "F"), Cells(n + 5, "F")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=$AD2=""○""")
            Cells(2, "F").Select
            Range(Cells(2, "F"), Cells(n + 5, "F")).FormatConditions(2).Font.Strikethrough = True

       Call Range(Cells(2, "G"), Cells(n + 5, "G")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=$AE2=""○""")
            Cells(2, "G").Select
            Range(Cells(2, "G"), Cells(n + 5, "G")).FormatConditions(2).Font.Strikethrough = True

    End Sub
（やっち） 2020/08/25(火) 13:33

 再現できないのでわかりません。
 下記のコードを実行して、結果を書き込んでもらえませんか？
    Sub 出力()
        Dim a As Variant
        For Each a In Cells.FormatConditions
            Debug.Print a.Formula1
        Next a
    End Sub

（稲葉） 2020/08/25(火) 18:00

おはようございます。
いただいたコードですが、
　Debug.Print a.Formula1
が1004エラーで実行できませんでした。
（やっち） 2020/08/26(水) 10:23

 もう少し自分でやってみてくれません？
 Debug.Print Cells.FormatConditions.Countで設定されてる条件付き書式の個数調べるとか。

 再現できないんだから、コード以外の要因、シートにイベントが仕込んであるとか想定して方がよさそうですね。

（稲葉） 2020/08/26(水) 10:51

申し訳ありません。

コードの順番と指定した番号を下記のように操作したところ、求めていた条件付き書式のみとすることができました。
しかしこの修正後、
=$AD2="○"を条件として入力したいけれど=$AD1048555="○"や=$AD1="○"になってしまう現象が再発しました。
どうやらFormatconditions.Addの直前に選択しているセルで数値が変わるようなのですが、どういうロジックで条件式の数値が変更されるのか教えていただきたく思います。

Formatconditions.Addの直前に Cells(2, 1).Selectを入れることでなんとか実用可能にはなりました。
何度もご指導いただきありがとうございます。

    Sub 入力()
       Dim n As Long
       n = Cells(Rows.Count, "F").End(xlUp).Row 'nをF列の最終入力セルの行番号とする
       Cells.FormatConditions.Delete
            'Cells(2, 1).Select
       Call Range(Cells(2, "G"), Cells(n + 5, "G")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=$AE2=""○""")
            Cells(2, "G").Select
            Range(Cells(2, "G"), Cells(n + 5, "G")).FormatConditions(1).Font.Strikethrough = True

       Call Range(Cells(2, "F"), Cells(n + 5, "F")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=$AD2=""○""")
            Cells(2, "F").Select
            Range(Cells(2, "F"), Cells(n + 5, "F")).FormatConditions(1).Font.Strikethrough = True

       Call Range(Cells(2, "C"), Cells(n + 5, "C")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=COUNTIFS($G$2:$G$" & n + 5 & ",$G$2,$R$2:$R$" & n + 5 & ",$R$2)>1")
            Cells(2, "C").Select
            Range(Cells(2, "C"), Cells(n + 5, "C")).FormatConditions(1).Interior.Pattern = xlGray75
            Range(Cells(2, "C"), Cells(n + 5, "C")).FormatConditions(1).Interior.PatternColor = RGB(255, 153, 255)

       Call Range(Cells(2, "A"), Cells(n + 5, "AE")).FormatConditions.Add(Type:=xlExpression, Operator:=xlEqual, Formula1:="=CELL(""row"")=ROW()")
            Range(Cells(2, "A"), Cells(n + 5, "AE")).FormatConditions(4).Interior.Pattern = xlGray50
            Range(Cells(2, "A"), Cells(n + 5, "AE")).FormatConditions(4).Interior.PatternColor = RGB(0, 0, 255)

    End Sub

（やっち） 2020/08/26(水) 11:50

 2007と2013（当方）で仕様が違うのかも。
 昔からおかしな数式になるのは、選択セルの左上にアクティブセルが無いと、おかしな数式になる

 下のコードを手作業で実行すると、A1の数式がおかしなことになってるけど、マクロで実行すると狙った通りになる。
 ちょっとやってみてください。
    Sub test()
        Range("A1:A10").Select
        Range("A2").Activate
        With Selection.FormatConditions.Add(xlExpression, xlEqual, "=B1=""○""")
            .Interior.Color = vbRed
        End With
    End Sub
（稲葉） 2020/08/26(水) 16:48

こんな仕様になっていたなんて知りませんでした…
.Activeを挟んで書き直してみます。

日付をまたいで長い間回答いただきありがとうございました。
（やっち） 2020/08/27(木) 11:18

コメント返信：

[ 一覧(最新更新順) ]

[[20200825110401]] 『VBAで条件付き書式を設定』（やっち） ページの最後に飛ぶ

[[20200825110401]]　『VBAで条件付き書式を設定』（やっち）　ページの最後に飛ぶ