2014/08/16

Excel でコピー&ペーストしてもセルの条件付き書式を消さない方法


Excel の条件付き書式が設定されたセルに、他のセルの内容をコピー&ペーストすると、条件付き書式がクリアされ消えてしまいます。これは Excel の仕様上仕方なく、設定で回避することはできません。

この問題を解決するのに結構はまったのですが、該当セルが編集されたときに、条件付き書式を VBA で再設定することで解決したので、ここでその方法を載せておきます。

まずは、実際に条件付き書式をセルに設定してみましょう。

ここでは、A1 セルに値が入力されたとき背景色を変える条件付き書式を設定します。

pic01

この状態で、A1セルに値を入力すると、以下のようにセルに背景色が付きます。

pic02

しかし、例えば B1 セルの内容を A1 セルにコピー&ペーストした後に、同じように値を入力しても背景色は付きません。これは A1 セルの条件付き書式がクリアされてしまったためです。

pic03

この問題を解消するには、VBA でセルに条件付き書式を再設定します。

その方法ですが、まず [マクロの記録] で A1 セルに条件付き書式を設定します。そうすると以下のようなマクロが作成されます。

Sub Macro1()
    Range("A1").Select
    Cells.FormatConditions.Delete
    Range("A1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$1<>"""""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

Color は RGB で指定した方が後で分りやすいですが、ここではそのままで行きましょう。

Visual Basic Editor を起動して、sheet1 の Worksheet_SelectionChange イベントに以下のように記録したマクロをコピー&ペーストします。青い字の部分は、追加で記述したものですが、説明は後述します。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

   'A1セルが更新された時のみ後述の処理を行う
    If Intersect(Target, Range("A1")) Is Nothing Then
        Exit Sub
    End If

    Range("A1").Select
    Cells.FormatConditions.Delete
    Range("A1").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=$A$1<>"""""
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 49407
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False
End Sub

この状態で、先ほどと同じように B1 セルの内容を A1 セルにコピー&ペーストしてから、値を入力すると以下のように条件付き書式が設定されていることが分ります。

pic04

さて、先ほど以下の部分の説明を飛ばしましたが、この解説も行います。

   'A1セルが更新された時のみ後述の処理を行う
    If Intersect(Target, Range("A1")) Is Nothing Then
        Exit Sub
    End If

Intersect 関数は、1つ目のセルが、2つ目のセルの範囲に含まれているか判定します。Nothing の場合は、範囲に含まれないことを意味します。なお、Target は更新されたセルを意味します。

ですので、上記のコードは、更新されたセルが A1 セルでないのなら処理を行わない、という内容になります。これをやらないと、全てのセルで処理を行うことになり、パフォーマンスが悪くなりますし、なにより正しく動作しません。

これで条件付き書式をコピー&ペーストしてもクリアされないようにすることができました。

後は、コードをカスタマイズすることで、思い通りの動作をするようになります。


スポンサーリンク


このエントリーをはてなブックマークに追加




Twitter ではブログにはない、いろんな情報を発信しています。


コメント

コメントを書く



プロフィール

  • 名前:fnya
    経歴:
    SE としての経験は15年以上。様々な言語と環境で業務系システム開発を行い、セキュリティ対策などもしていました。現在は趣味SE。

    Twitter では、ブログでは取り上げない情報も公開しています。


    ブログについて

    このブログは、IT、スマートフォン、タブレット、システム開発などに関するさまざまな話題を取り上げたり、雑感などをつづっています。

    現在、Enty で支援を受け付けています。もしよければご支援ください。



    >>ブログ詳細
    >>自作ツール
    >>運営サイト
    >>Windows 10 まとめ

    Twitter のフォローはこちらから Facebook ページはこちら Google+ページはこちら RSSフィードのご登録はこちらから