以前Excel-VBAを使ってExcelを便利にする小技を動画で紹介しました。
動画の前半ではセルの移動方向を1クリックで切り替える方法を紹介していいます。
この方法についての記事はこちらを参照ください。
今回は動画の後半で紹介している選択セルの行列にハイライト表示をする方法を紹介します。動画のように1クリックでハイライト表示のON/OFFを切り替える方法についても紹介します。
Excelで入力作業をしているとき、データ数が増えると現在選択しているセルがどこの行または列なのかわかりにくくなります。
そんなときに選択しているセルの行と列に色をつけることができると一目で確認できて便利です。
これは条件付き書式とマクロを併用することで実現できます。
条件付き書式の設定
まず行と列に色をつけるには条件付き書式の数式に次の式を設定します。
=OR(CELL(“row”)=ROW(), CELL(“col”)=COLUMN())
CELL(“row”)で最後に変更したセルの行番号、CELL(“col”)で列番号が取得できます。
ROW()およびCOLUMN()はそれぞれ自身の行番号および列番号になります。
つまりこの式は自身の行番号か列番号が最後に変更したセルの行番号か列番号と一致したときにTRUEを返す式です。
この条件式で書式(塗りつぶしの色)を設定すれば選択したセルの行と列に指定した色が塗りつぶされます。
このままでは選択しているセルも塗りつぶされてしまいます。選択セルは入力作業を行うセルなので塗りつぶさないほうが見やすいです。そこで次の条件付き書式も設定しておきます。
=CELL(“address”) = ADDRESS(ROW(), COLUMN())
これはCELL(“adress”)で最後に変更したセルのアドレス、ADRESS(ROW(),COLUMN())で自身のアドレスになり、両者が一致したときにTRUEを返します。
このときの書式で塗りつぶしの色を「色なし」にしておきます。
ただ、これらの条件付き書式を設定しただけではうまくいきません。セルの選択場所を切り替えたときに条件付き書式の数式を再計算する必要があるからです。
これを実現するためにマクロを活用します。
セル選択で再計算させるマクロ
ここではSheetSelectionChangeイベントを使います。
これはいずれかのワークシートで選択範囲を変更したときに発生するイベントです。
ブックモジュールにコードを記述します。
Alt + F11 でVBEを起動し、登録するブックのVBAProjectのThisWorkbook をダブルクリックして表示されるコードウィンドウに次のコードを記述してください。
'//選択セルが変わるたび再計算する
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.Calculate
End SubApplication.Calculate で再計算を行います。つまり選択範囲が変更されたときに強制的に再計算を実行させていることになります。
マクロを使ってハイライトON/OFFを切り替える
条件付き書式で設定したハイライト表示をOFFにするには条件付き書式を削除する必要があります。
しかし、条件付き書式の設定→削除をその都度実施するのは手間がかかります。
そこでこれらの作業(条件付き書式の設定および削除)を行うマクロを作成することで1クリックでハイライト表示のON/OFFを切り替えられるようにします。
マクロのコードは以下のとおりです。(こちらのコードは標準モジュールに記述します)
'//セルハイライト処理のメインプロシージャ
Public Sub CellHighlight()
Dim exp1 As String '//条件式1
Dim exp2 As String '//条件式2
Dim preExp As String '//設定済条件式
Dim cnt As Long '//設定されている条件付き書式の数
Dim i As Long
Dim flg As Boolean '//ハイライトOFFしたか判定
flg = False
exp1 = "=CELL(""address"") = ADDRESS(ROW(), COLUMN())"
exp2 = "=OR(CELL(""row"")=ROW(), CELL(""col"")=COLUMN())"
cnt = Cells.FormatConditions.Count
'//条件付き書式が存在するならハイライト設定済かを判定
If cnt <> 0 Then
For i = cnt To 1 Step -1
preExp = ""
'//ハイライト設定済ならハイライトOFF
On Error Resume Next
preExp = Cells.FormatConditions(i).Formula1
On Error GoTo 0
If preExp = exp1 Or preExp = exp2 Then
Call HighlightOFF(i)
flg = True
End If
Next i
End If
'//ハイライト設定がされていなければハイライトON
If flg = False Then
Call HighlightON
End If
End Sub
'//ハイライト処理をON
Private Sub HighlightON()
Dim fc As FormatCondition '//条件付き書式
Dim exp As String '//数式
'//選択セルは背景色なし
exp = "=CELL(""address"") = ADDRESS(ROW(), COLUMN())"
Set fc = Cells.FormatConditions.Add(xlExpression, , exp)
fc.Interior.ColorIndex = 0
'//選択セルと同列、同行をハイライト
exp = "=OR(CELL(""row"")=ROW(), CELL(""col"")=COLUMN())"
Set fc = Cells.FormatConditions.Add(xlExpression, , exp)
fc.Interior.ColorIndex = 44
End Sub
'//ハイライト処理OFF
Private Sub HighlightOFF(i As Long)
Cells.FormatConditions(i).Delete
End Sub
プロシージャ名 CellHighlight がメインのプロシージャになります。
このプロシージャを実行すると現在設定されている条件付き書式を検索し、すでにハイライト表示が設定済ならその条件付き書式を削除するプロシージャ HighlightOFF を呼び出し、設定されていなければ条件付き書式を追加するプロシージャ HightlightON を呼び出すようになっています。
1クリックでON/OFFを切り替えられるようにするにはクイックアクセスツールバーにマクロを登録します。登録するのはメインプロシージャであるCellHighlightのマクロのみでOKです。
クイックアクセスツールバーにマクロを登録する方法は次の記事を参考にしてください。
すべてのブックで使えるようにするにはマクロを個人用マクロブックに保存します。この場合、 SheetSelectionChange イベントのマクロは個人用マクロブック(VBAProject(PERSONAL.XLSB))のブックモジュールに次のように記述してください。
Private WithEvents app As Application
Private Sub Workbook_Open()
Set app = Application
End Sub
'//選択セルが変わるたび再計算する
Private Sub app_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.Calculate
End Sub個人用マクロブックについてはこちらの記事を参考にしてください。
コメント