条件付き書式をコピペするとルールが増えることは回避できない
Excelの条件付き書式は自動で背景色を変えてくれたりする便利な機能ですが、対象セルをコピペすると条件付き書式のルールがどんどん増えて操作が重くなるという問題があります。
残念ながらこれを回避する方法はありません。
ちなみにこの事象についてMicrosoftは以下のページで「仕様だ」と言っています。
https://support.microsoft.com/ja-jp/help/2537195
解決方法は「条件付き書式のルールをクリアして再設定しろ」とあります。
おいおい。
条件付き書式の設定は本当に大変です。便利な機能ですがその設定は面倒です。それをやりなおせ、というのはいかがなものかと思います。
仕様だろうがなんだろうが問題があることに変わりはありません。
コピペで増えたルールをVBAで整理は可能
とは言ってもどうにか対応できないものでしょうか。コピペで増えるのは回避できませんが、増えたルールをVBAで整理する、という方法は可能です。
ここでは条件付き書式で、数式を設定した場合で、条件に =、<>、>、<、>=、<=、のいずれかが設定されている場合に重複を整理するコードを紹介します。
セルの値が「次の値の間」の条件や、「上位」「下位」「平均」などの条件付き書式には対応していません。（自分があまり使わないので）
以下がコードになります。
条件付き書式を整理するVBAコード
ユーザー定義型（構造体）
内部処理で使うための構造体です。
標準モジュールの先頭に書いてください。
|
1 2 3 4 5 6 7 |
'// 条件付き書式設定構造体 Public Type ST_FORMATCONDITIONS sSetString As String '// 数式 sCol As String '// 数式文字列の列座標 sValue As String '// 数式文字列の検索対象文字列 bDeleteTarget As Boolean '// 削除する書式かどうかの判定フラグ（True：削除する） End Type |
関数本体
関数本体は標準モジュールやフォームのどこに書いても大丈夫です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 |
Sub 条件付き書式の整理() Dim i Dim iMax Dim oCondition As FormatCondition Dim tAr() As ST_FORMATCONDITIONS Dim t As ST_FORMATCONDITIONS Dim iCondition Dim iAr Dim v Dim sCol Dim sValue Dim iLen Dim r As Range Dim rNow As Range Dim sRow Dim sCondition Application.ScreenUpdating = False Set rNow = Selection '// 視点縦座標を取得（全行の場合は1、そうでない場合はAddressの先頭行を対象とする） sRow = "" v = Split(rNow.Address, ":") For i = 1 To Len(v(0)) If IsNumeric(Mid(v(0), i, 1)) = True Then sRow = sRow & Mid(v(0), i, 1) End If Next If sRow = "" Then sRow = "1" End If iMax = Selection.FormatConditions.Count ReDim tAr(0) '// 条件付き書式の設定数ループ For i = 1 To iMax Set oCondition = Selection.FormatConditions(i) t.sSetString = oCondition.Formula1 t.sSetString = Mid(t.sSetString, 2) '// 条件判定 sCondition = "" If InStr(1, t.sSetString, "<>") > 0 Then sCondition = "<>" ElseIf InStr(1, t.sSetString, ">=") > 0 Then sCondition = ">=" ElseIf InStr(1, t.sSetString, "<=") > 0 Then sCondition = "<=" ElseIf InStr(1, t.sSetString, "=") > 0 Then sCondition = "=" ElseIf InStr(1, t.sSetString, ">") > 0 Then sCondition = ">" ElseIf InStr(1, t.sSetString, "<") > 0 Then sCondition = "<" End If v = Split(t.sSetString, sCondition) sCol = "" For iLen = 1 To Len(v(0)) If (IsNumeric(Mid(v(0), iLen, 1)) = False) Then sCol = sCol & Mid(v(0), iLen, 1) End If Next t.sCol = sCol t.sValue = v(1) t.bDeleteTarget = False iAr = UBound(tAr) '// 配列ループ（同じ種類の書式設定を配列にまとめる） For iCondition = 0 To iAr If (tAr(iCondition).sValue = t.sValue) Then t.bDeleteTarget = True End If Next If (UBound(tAr) <> 0 Or tAr(0).sSetString <> "") Then ReDim Preserve tAr(UBound(tAr) + 1) End If tAr(UBound(tAr)) = t Next '// 重複している条件を削除。削除しない分は適用先を変更。 i = UBound(tAr) Do If (tAr(i).bDeleteTarget = True) Then '// 重複分を削除 Selection.FormatConditions(i + 1).Delete Else '// 残留分の適用先を列全体に変更 Set r = Range(rNow.Address) Selection.FormatConditions(i + 1).ModifyAppliesToRange r '// 数式のセル座標を修正 Selection.FormatConditions(i + 1).Modify Type:=xlExpression, Formula1:="=" & tAr(i).sCol & sRow & sCondition & tAr(i).sValue End If If (i = 0) Then Exit Do End If i = i - 1 Loop rNow.Select Application.ScreenUpdating = True End Sub |
処理の概要
コードが長いので詳細は省略します。
選択されている列（縦）に条件付き書式のルールの数（コピペで増えた数）がいくつあるのかを確認し、それぞれの重複を削除して適用先を修正します。
列選択されていることが前提になります。
そのため行選択での本VBA処理の実行は挙動未確認です。
利用の際の注意点
複数の列に対して同じルールの条件付き書式を設定する場合がありますが、その場合はその複数列を選択して「条件付き書式の整理()」関数を実行してください。
誤って、同じルールの複数列を選択せずに１列ずつ「条件付き書式の整理()」関数を実行すると適用先（設定範囲）が変わってしまいます。
この場合はUndo(Ctrl + Z)が利きませんので残念ですがブックをいったん閉じてから開きなおしてもらう必要があります。
使い方
条件付き書式を設定している行範囲を選択して、「条件付き書式の整理()」関数を実行します。
条件付き書式のルールが複数ある場合は、そのルールごとに行を選択して「条件付き書式の整理()」関数を実行します。
全列を選択して一度に整理しようかと最初は思ったのですが、（面倒なので）やめました。
以下が使い方の例です。
まず、条件付き書式を設定します。
ここではA列からC列の3列と、D列とE列の2列にそれぞれ設定しています。
次に、2行目をコピーして6行目に貼り付けます。この時点で条件付き書式が2つに分かれます。
重複を整理する列を選択します。
選択は同じ条件付き書式の列をまとめて選択します。
ここではD列とE列の2列を選択します。
条件が異なるA列からC列は選択しません。
あとは「条件付き書式の整理()」関数を実行します。
実行後にルールを確認すると重複が整理されています。
A列からC列も同様に行うことで重複が解消します。