excel2010条件付き書式後にVBAにて処理

Question

VBAビギナーのmaruです

Excel2010のワークシート上の範囲(Range（”E12:K120“）)内の
各セルに条件付き書式（複数あり）にてフォント色を赤く（RGB 255,0,0）
変更したセル内のデータ（数字）のみ削除（””)するVBAを行いたいのですが

以下のコードでは、「セルの書式」で設定したフォント色で削除されてしまい
条件付き書式で変更された赤文字に反映されません
ご教授お願い致します

Private Sub CommandButton4_Click()
 
'データ入力セル内の赤文字の削除
 
 Dim rd As Range
  
 For Each rd In Range("E12:K120")
 
 For i = 1 To rd.FormatConditions.Count
 
 If rd.FormatConditions(i).Font.Color = RGB(255, 0, 0) Then
 
 rd.Value = ""
 
 End If
 
 Next
 
 Next

End Sub

以上、宜しくお願い致します

tom04 · Accepted Answer

こんにちは！

横からお邪魔します。
VBAで条件付き書式のフォント色を調べる　→　指定色の場合データを消去
という操作は
条件付き書式の条件を当てはめて、該当する場合にデータ消去！
という方法が一般的だと思いますが、

Excel2010からは
「DisplayFormatオブジェクト　」
というものが追加されているようですので、
見た目のフォント色（セル色）で区別ができるようです。

Sub Sample1()
    Dim c As Range
    For Each c In Range("E12:K120")
        If c.DisplayFormat.Font.Color = RGB(255, 0, 0) Then
            c.ClearContents
        End If
    Next c
End Sub

としてみてはどうでしょうか？m(_ _)m

MarcoRossiItaly · Answer

No.6 です。

No.7 さんの DisplayFormat オブジェクトが良さそうですね。当初の方針どおり、設定済みの条件付き書式を利用して対象セルを特定するという場合は。

手元に試せる環境がないのですが、問題ないようでしたら、回答 No.6 よりも No.7 を優先してください。条件によらないセルの書式で赤字に設定している箇所は、ないというお話でしたね？ある場合は、そのセルの値まで消えてしまうかもしれませんね。

これは動詞の display ではなく、名詞の display のようですね。画面での表示状態を表すオブジェクトということのようです。

MarcoRossiItaly · Answer

No.3 です。

＞当方のコードでは、各セルに設定した条件付き書式のルールに文字色を赤くする設定があるセルのみが、削除されることが分かりました

そのとおりです。

＞以下のコードでは、「セルの書式」で設定したフォント色で削除されてしまい条件付き書式で変更された赤文字に反映されません
＞文字色が、条件に合わないセル（赤文字以外）も前記設定があると削除されてしまい……

「『条件』に合わないのに『設定』があると削除されてしまう」とは、条件付き書式の条件式に適合する（TRUE である）セルのみ値を削除したいのに、FALSE、つまり変色していないが条件付き書式が設定されているセルまで消えるというのは、望んでいない動作だという意味ですか？

お示しのコードでは処理（値を消すこと）の対象とするセルを、条件付き書式のフォントの色「のみ」で特定しています。したがって当然、TRUE か FALSE かは、処理の結果に全く影響しません。

ということは、両者を区別するには、基本的には対象セルを絞り込む条件をもう少し追記してあげればいいということになります。

ただ、一口に条件付き書式と言っても、指定の値を含む…とか上位または…とか、いろいろ種類があるので、それによって取るべき対応も変わってきそうです。

下のコードは、文字色として RGB(255, 0, 0) を指定してあり、なおかつ条件式（論理式）を条件とする書式の場合です。

通常、「コード内においても条件付き書式の条件式と同様の手続をなぞって場合分けをする」ことで、各セルを処理するというほうが普通の方法かもしれません。ただ条件付き書式（FormatCondition オブジェクト）から条件式を取得して処理に利用するということも一応は不可能でないことを示す意味で、下に載せます。ですから、お勧めという意味ではありません。

また、条件式に相対参照が含まれ、複数のセルを含む範囲に 1 つの条件式を設定している場合は、アクティブセルの位置によって、適切にアドレスを Offset させてあげなければならないといった問題もあります。「=a1=3」という条件式があって、先にマクロで A1 セルに記入されていた「3」が消えると、同一の条件で書式を指定していた B1 では「=b1=3」ではなく「=a1=3」で判定されてしまうことによって値が消えない…といった現象です。話がどんどん複雑になるので、下のコードではその対応は省きます。

Private Sub CommandButton1_Click()
　　Dim rd As Range, i As Integer
　　For Each rd In Range("E12:K120")
　　　　For i = 1 To rd.FormatConditions.Count
　　　　　　With rd.FormatConditions(i)
　　　　　　　　If (.Font.Color = RGB(255, 0, 0)) * Evaluate(.Formula1) Then rd.ClearContents
　　　　　　End With
　　　　Next i
　　Next rd
End Sub

end-u · Answer

提示コードの問題は、
「条件を満たすかどうかチェックせずに、書式の設定のみで判断しようとしている」点です。

rd.FormatConditions(i).Font.Color を見てますが、
「条件を満たせば赤」という設定であれば、ここには「赤」が設定されています。
条件を満たすかどうか、条件式を評価しなければなりません。
Formula1プロパティなどがこれにあたります。

ただ、
ver2003までは、この条件式を評価して判断する事ができましたが、
2007以降は仕様変更のため、この作業はかなり煩雑になります。

例えば
Dim r As Range
Dim f As FormatCondition
For Each r In Range("E10:K120")
　　For Each f In r.FormatConditions
　　　　Debug.Print r.Address, f.Formula1, f.Font.Color
　　Next
Next
こんなコードを実行し、
f.Formula1 の式を評価する事で条件を満たしている事を判断できるか、確認してみてください。
対象セルが違うのに、f.Formula1のアドレスに変化がなかったりする場合は、相対関係が正しく捉えられません。

>..ご指摘の
>部分を当環境に合わせ、変更しながら実施しておりますが、
>エラーが生じております、..
どう変更して、どこでどのようなエラーが出ているのか提示があれば
追加でアドバイスもできるかもしれません。
ただ、「条件付き書式」以外で、手動で「赤」に設定したセルがあると
結果としての書式のみでは判断できませんよね。
別のアプローチが必要になります。

>エラーチェックとして条件付き書式を設定し、文字色が赤くなったセルをエラーデータ
>として削除します
エラーチェックのロジックが事前にわかっているのなら、
このチェックを行うコードをVBAで書けば良いです。
必要なら一次チェックでフォントを赤にして、
確認メッセージを出した後削除するとか。
「条件付き書式」にこだわらなくてもよいかと。

TAKA_R · Answer

質問です。
その範囲の中に「条件付書式で設定したわけではない」のに、赤い文字はあるんでしょうか。
ないのなら、フォントの赤で条件を絞って消しても良いかと思います。

MarcoRossiItaly · Answer

お示しのコードに大きな問題はないように思います。次の 3 点を確認してみてください。

●開発タブのデザインモードがオンになっている場合は、オフにしてから再度、コマンドボタンをクリック。
● Font と Interior プロパティを錯覚していないか。
● VBE で「ツール > オプション > 変数の宣言を強制する」（Option Explicit）をチェックしている（お勧め）という場合は、変数 i の宣言文を入れてください。

end-u · Answer

'-----------------------------------------------------------
'【ver2007以降】
Sub test() 'ActiveSheetに対して処理。
　　Dim ws　As Worksheet
　　Dim tmp As String
　　Dim buf As String
　　Dim n　 As Long
　　Dim r　 As Range
　　Dim ri　As Range

Application.ScreenUpdating = False
　　'作業用mhtファイル名を設定。 _
　　 同名既存ファイルがあれば上書きするので注意。
　　tmp = Application.DefaultFilePath & "	emp.mht"
　　Set ws = ActiveSheet
　　ActiveWorkbook.PublishObjects.Add( _
　　　　　　xlSourceSheet, tmp, _
　　　　　　ws.Name, "", _
　　　　　　xlHtmlStatic).Publish True
　　'Format検索
　　With Application.FindFormat
　　　　.Clear
　　　　.Font.Color = vbRed
　　End With

With Workbooks.Open(tmp)
　　　　With .Sheets(1).Range("E12:K120")
　　　　　　'対象セルを"#n/a"に変換してまとめて取得してるので _
　　　　　　 直値のエラー表示がある場合は別の手法を。
　　　　　　.Replace What:="*", _
　　　　　　　　　　 Replacement:="#n/a", _
　　　　　　　　　　 LookAt:=xlPart, _
　　　　　　　　　　 SearchFormat:=True
　　　　　　On Error Resume Next
　　　　　　Set r = .SpecialCells(xlCellTypeConstants, xlErrors)
　　　　　　On Error GoTo 0
　　　　End With
　　　　If Not r Is Nothing Then
　　　　　　For Each ri In r
　　　　　　　　'VBEイミディエイトウィンドに対象セルアドレスを書き出し。 _
　　　　　　　　 ↓本番では必要なし。
　　　　　　　　Debug.Print ri.Address(0, 0)

'ws.Range(ri.Address).ClearContents '■この行を活かせば削除
　　　　　　Next
　　　　End If
　　　　.Close False
　　End With
　　'作業用mhtファイル削除。
　　Kill tmp
　　Set r = Nothing
　　Set ws = Nothing
　　Application.ScreenUpdating = True
End Sub
'-----------------------------------------------------------
上記、昔書いた事があるコードを焼き直ししたものです。
mhtファイルに吐き出すと条件付き書式の結果が固定される事を利用してます。
>VBAビギナー..
だとちょっと厳しいかもしれませんが
内容を理解した上で活用できそうだったらどうぞ。

kagakusuki · Answer

その様な場合は、条件付き書式で色が付いているセルを削除するのではなく、どの様な条件を満足した場合に、その条件付き書式によって色が付くのかを調べて、その条件を満たしているセルを削除する様なVBAにされた方が良いと思います。

excel2010条件付き書式後にVBAにて処理

こんにちは！

この回答への補足

No.6 です。

No.3 です。

この回答への補足

提示コードの問題は、

この回答への補足

質問です。

この回答への補足

お示しのコードに大きな問題はないように思います。

この回答への補足

'-----------------------------------------------------------

この回答への補足

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング