Back

About MrExcel
Consulting Services
Learn Excel Resources
Challenge of the Month
MrExcel Seminars

Message Board

MrExcel Store
Podcast
Search
Contact
Home

 

 

Past Tip of the Day

 

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.

By Bill Jelen on 25-Oct-2001

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

 

Excel is a registered trademark of the Microsoft® Corporation.

All contents Copyright 1998-2008 by MrExcel Consulting.