選択セルの行と列をハイライト表示する方法

以前Excel-VBAを使ってExcelを便利にする小技を動画で紹介しました。

動画の前半ではセルの移動方向を1クリックで切り替える方法を紹介していいます。

この方法についての記事はこちらを参照ください。

セル移動方向の切り替え

Excel 作業でセルに値を入力したあとにEnterキーを押すと、デフォルトの設定ではセルの選択先が下方向に移動します。 この移動方向を変更するにはファイル→オプション→詳細設定で 「Enter キーを押したら、セルを移動する」 の「方向」...

tech-paclab.com

2019.06.04

今回は動画の後半で紹介している選択セルの行列にハイライト表示をする方法を紹介します。動画のように1クリックでハイライト表示のON/OFFを切り替える方法についても紹介します。

Excelで入力作業をしているとき、データ数が増えると現在選択しているセルがどこの行または列なのかわかりにくくなります。

そんなときに選択しているセルの行と列に色をつけることができると一目で確認できて便利です。

これは条件付き書式とマクロを併用することで実現できます。

条件付き書式の設定

まず行と列に色をつけるには条件付き書式の数式に次の式を設定します。

CELL(“row”)で最後に変更したセルの行番号、CELL(“col”)で列番号が取得できます。

ROW()およびCOLUMN()はそれぞれ自身の行番号および列番号になります。

つまりこの式は自身の行番号か列番号が最後に変更したセルの行番号か列番号と一致したときにTRUEを返す式です。

この条件式で書式(塗りつぶしの色)を設定すれば選択したセルの行と列に指定した色が塗りつぶされます。

このままでは選択しているセルも塗りつぶされてしまいます。選択セルは入力作業を行うセルなので塗りつぶさないほうが見やすいです。そこで次の条件付き書式も設定しておきます。

これはCELL(“adress”)で最後に変更したセルのアドレス、ADRESS(ROW(),COLUMN())で自身のアドレスになり、両者が一致したときにTRUEを返します。

このときの書式で塗りつぶしの色を「色なし」にしておきます。

ただ、これらの条件付き書式を設定しただけではうまくいきません。セルの選択場所を切り替えたときに条件付き書式の数式を再計算する必要があるからです。

これを実現するためにマクロを活用します。

セル選択で再計算させるマクロ

ここではSheetSelectionChangeイベントを使います。

これはいずれかのワークシートで選択範囲を変更したときに発生するイベントです。

ブックモジュールにコードを記述します。

'//選択セルが変わるたび再計算する
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Application.Calculate
End Sub

Application.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です。

クイックアクセスツールバーにマクロを登録する方法は次の記事を参考にしてください。

マクロをクイックアクセスツールバーに登録する方法

マクロをクイックアクセスツールバーに登録すれば、すべてのワークシートから1クリックでマクロを実行できるようになります。 また適用範囲を「すべてのドキュメント」にすることで他のワークブックでも同じマクロを実行できるようになります。 ここではク...

tech-paclab.com

2019.05.07

すべてのブックで使えるようにするにはマクロを個人用マクロブックに保存します。この場合、 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

個人用マクロブックについてはこちらの記事を参考にしてください。

個人用マクロブック

個人用マクロブックとは 個人用マクロブックは「PERSONAL.XLSB」という名称のブックです。 次のような特徴があります。 マクロの記録を行うことで自動的に作成されます。個人用マクロブックに保存されたマクロはそのExcelのすべてのブッ...

tech-paclab.com

2019.05.06