Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to highlight active cell or selection in Excel?

If you have a large worksheet, maybe it is hard for you to find out the active cell or active selection at a glance. But, if the active cell/section has an outstanding color, to find out it will not be a problem. In this article, I will talk about how to automatically highlight the active cell or selected range of cells in Excel.

Highlight active cell or selection with VBA code

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


arrow blue right bubble Highlight active cell or selection with VBA code


The following VBA code can help you to highlight the active cell or a selection dynamically, please do as follows:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Then choose ThisWorkbook from the left Project Explorer, double click it to open the Module, and then copy and paste following VBA code into the blank Module:

VBA code: Highlight active cell or selection

Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
'Update 20140923
Static xLastRng As Range
On Error Resume Next
Target.Interior.ColorIndex = 6
xLastRng.Interior.ColorIndex = xlColorIndexNone
Set xLastRng = Target
End Sub

doc-highlight-active-cell-1

3. Then save and close this code, and go back to the worksheet, now, when you select a cell or a selection, the selected cells will be highlighted, and it will be dynamically moved as the selected cells changes.

Notes:

1. If you cannot find the Project Explorer Pane in the window, you can click View > Project Explorer in the Microsoft Visual Basic for Applications window to open it.

2. In the above code, you can change .ColorIndex = 6 color to other color you like.

3. This VBA code can be applied to all worksheets within the workbook.

4. If there are some colored cells in your worksheet, the color will be lost when you click the cell and then move to other cell.


Related article:

How to auto-highlight row and column of active cell in Excel?


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 80% Excel problems.

  • Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  • Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  • No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  • Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    pyroto · 10 months ago
    Anyway you can do the left adjacent cell to highlight instead of active cell? Or a range of cells around the activecell?
  • To post as a guest, your comment is unpublished.
    Joa · 1 years ago
    Thank you, but use this with caution. It has the undesirable counter effect of not letting undo. Is there any solution for that?
  • To post as a guest, your comment is unpublished.
    Sud · 1 years ago
    But i am unable to do undo option and lose the previously assigned cell color also.
    Please provide solution
  • To post as a guest, your comment is unpublished.
    Pravin Shinde · 1 years ago
    Perfect solution as I always tend to loose the track of the highlighted cell during "Find and Select" operation. Thanks a lot.
  • To post as a guest, your comment is unpublished.
    Rana Banik · 1 years ago
    Great!!
    But what if I don't want to lose the previously assigned cell color?

    That would be greater to know.
    • To post as a guest, your comment is unpublished.
      Barney · 1 years ago
      Use a conditional format on the cells you want to protect.
    • To post as a guest, your comment is unpublished.
      Mark Anderson · 1 years ago
      Instead of "ColorIndex" which has a limited set of 255 values (meaning any custom colors are lost), use "Color" instead. You'll need to store the old value in a new static long variable and also generate the highlight color you want with the RGB function. The only problem with having this code active is that you can no longer change the active cell color as it changes back to what it was before you clicked it. I suggest that if you need to change the highlight colors on the sheet, do so with macros disabled or change the sub name for a second so it doesn't trigger when you are clicking on cells, then just rename it back to normal to "re-activate" it. Here is my code:

      Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)

      Static xLastRng As Range
      Static xLastRngColor As Long

      On Error Resume Next

      xLastRng.Interior.Color = xLastRngColor
      xLastRngColor = Target.Interior.Color
      Target.Interior.Color = RGB(255, 255, 0) 'compose whatever highlight color you want with RGB values, I am using yellow here
      Set xLastRng = Target

      End Sub
    • To post as a guest, your comment is unpublished.
      Lee · 1 years ago
      Yes, I would like to know this as well. How would you keep the previously selected cells highlight for tracking purposes?