【VBA】Excel 条件付き書式のコピペと壊れたゴミルールの削除

Excel VBA 一覧

ご訪問ありがとうございます。

Googleシートマスターのひろしです。

前回

【VBA】Excel チェックボックスがONのシートを印刷する

EXCELでチェックボックスの値を手軽に取得できます。 VBAから簡単に印刷できます。

今回は、とっても価値の高い方法をお伝えします。

これを知ることであなたは、

セルの値に従って背景色を付加するといった条件付き書式が簡単に設定できます。
コピペ等で条件付き書式がずれても簡単に修正できます。
条件付き書式のみをコピペすることができます。

逆に、知らないと

せっかく、条件付き書式を設定してもデータのコピペを繰り返していると
条件がぐちゃぐちゃになってしまい。その修正に手間取ることになります。

なので、サクッとマスターして

あのー、条件付き書式がぐちゃぐちゃになるのって、なんとかならないですか？

あー、VBAでやればスッキリするよ。

と答えてあげてください。

まず、マインドセットします。

マインドセット
条件付き書式は、数式で指定しろ

条件付き書式は、「数式を使用して、書式設定するセルを決定」だけを使用しましょう
これだけマスターすればいいので、最も効果的な方法です。

動画はこちら

シートはこちら

*クリックすることでシートが開きます。(コピーしてお使いください)

スクリプト

以下のスクリプトを貼り付けます。

Sub 条件付き書式()
'


 Cells.FormatConditions.Delete  '既存の条件付き書式をクリア


With ActiveSheet.Range("$A1:$H34")
 .FormatConditions.Add Type:=xlExpression, Formula1:="=$H1=""*"""
'.FormatConditions(1).Interior.Color = "&HEED7BD"
 .FormatConditions(1).Interior.Color = RGB(189, 215, 238)
 .FormatConditions(1).StopIfTrue = False '「条件を満たす場合は停止」しない
End With
With ActiveSheet.Range("$A1:$H34")
 .FormatConditions.Add Type:=xlExpression, Formula1:="=OR($H1=1,$H1=2)"
 .FormatConditions(2).Interior.Color = "&HA6A6A6"
 .FormatConditions(2).StopIfTrue = False
 End With
End Sub

スクリプトをsheet1で実行すると以下のようになります。

ポイント

①全体の範囲を指定します。

②ルールの種類が
A) 「数式を使用して、書式設定するセルを決定」を選択した場合の
B) ルールを記載します。
※ルール内の”はエスケープするために”を付加します。

③色を指定します。
A) RGB(189,215,238) または
B) &HEED7BD (先頭2バイトと最後の2バイトを逆転して入力します)

④ 2個目の条件付き書式の場合(2)とします。

改善版

条件付き書式の番号を付加するより以下のようにやるほうがスッキリしますね。

Sub test()
Cells.FormatConditions.Delete '既存の条件付き書式をクリア

Dim rngCellArea As Range

'条件付き書式の範囲
Set rngCellArea = Range("$A1:$H34")

Set frmSetting = rngCellArea.FormatConditions.Add(Type:=xlExpression, Formula1:="=A1=""社A""")
frmSetting.Interior.Color = RGB(255, 100, 100)

Set frmSetting = rngCellArea.FormatConditions.Add(Type:=xlExpression, Formula1:="=A1=""社B""")
frmSetting.Interior.Color = RGB(145, 188, 110)

Set frmSetting = rngCellArea.FormatConditions.Add(Type:=xlExpression, Formula1:="=A1=""理科""")
frmSetting.Interior.Color = RGB(248, 203, 173)

Set frmSetting = rngCellArea.FormatConditions.Add(Type:=xlExpression, Formula1:="=A1=""国語""")
frmSetting.Interior.Color = RGB(146, 204, 204)

End Sub