MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Highlight The Active Cell

October 25, 2001 - by Bill Jelen

Karthik asks:

I am working on a event code for highlighting the active cell, basically show change the background color of the active cell. I used the change event and this changes the value of the active cell only when the value has changed.

What a cool question. The trick is to use the Worksheet_Selection change event instead of the Worksheet_Change event. This special event handler macro has to be entered on the code pane associated with your particular worksheet. A discussion of how to find this in the project pane is at this archived tip.

You would then enter the following code:

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not OldRng Is Nothing Then
        OldRng.Interior.ColorIndex = xlNone
    End If
    Target.Interior.ColorIndex = 6
    Set OldRng = Target
End Sub

This program uses a public variable called OldRng to remember the last range that was selected so that the color can be reset back to white when you select a new range.

Bill Jelen is the author / co-author of
Power Excel With MrExcel - 2017 Edition

This is the print book edition of "Power Excel with MrExcel - 2017 Edition" - by Bill Jelen. Master Pivot Tables, Subtotals, Visualizations, VLOOKUP, Power BI and Data Analysis.