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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
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.· 19 days agoI know that you proposed a solution but the solution comes with another problem as you described below: "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". This is not an acceptable alternative for me. I need to be able to change the colors in the cells when the macro is active. Do you have a solution for this this problem. If you come up with a solution for this problem please republish your corrected macro the new macro in full (rather than proposing patches to your original macro because I am technically challenged as far as macro are concerned).
- To post as a guest, your comment is unpublished.· 19 days agoI tried your macro by copying and pasting it into my Excel file. I did change one thing in the macro though. I changed the color index from 6 to 24 (to a color different than the colors I am currently using in my macro). It worked very nicely and I liked it a lot but then started causing a problem for me. I have a lot of cells filled with with colors in my Excel. The macro started removing the color fill from every cell touched. I don't know how to stop the macro's this odd behavior. I did not have any choice but to remove the macro from my file. I still like to use this macro if someone can help me to fix it.
- To post as a guest, your comment is unpublished.· 1 months agoThat was super helpful, thank you.
- To post as a guest, your comment is unpublished.· 2 years 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.· 2 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.· 2 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.· 2 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.· 3 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.· 2 years agoUse a conditional format on the cells you want to protect.
- To post as a guest, your comment is unpublished.· 2 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.· 2 years agoYes, I would like to know this as well. How would you keep the previously selected cells highlight for tracking purposes?
- To post as a guest, your comment is unpublished.· 2 years agoSee my comment above.
- To post as a guest, your comment is unpublished.· 3 years agoBrilliant! I've used your code to highlight a cell. It works perfectly for what I am doing. Using hundreds of hyperlinks to move between work sheets and work books. makes the traversing so much easier. Thank you for your little VBA script.