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.
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
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
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.
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.
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 10 months agoAnyway 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.· 1 years agoThank 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.· 1 years agoBut 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.· 1 years agoPerfect 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.· 1 years agoGreat!!
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.· 1 years agoUse a conditional format on the cells you want to protect.
To post as a guest, your comment is unpublished.· 1 years agoInstead 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
To post as a guest, your comment is unpublished.