Comments on: How to highlight active row and column in Excel

When viewing a large worksheet for a long time, you may eventually lose track of where your cursor is and which data you are looking at. To know exactly where you are at any moment, get Excel to automatically highlight the active row and column for you! Continue reading →

Comments page 2. Total comments: 56

  1. Hey, I've seen this before and thought it was neato but didn't know how to do it. Your instructions worked like a charm! thank you for sharing.

  2. Hi
    Codes worked well for a specific file. However, when applying them to two different Excel files, changes are synchronizing between sheets. Need help to separate rules for each sheet. Any advice appreciated."

  3. If your Excel file has an existing highlight do not use this vba code above!!!! It says "cells.interior.colorindex = 0". This will clear all the exisitng fill/highlight on your sheet!!!

  4. The VBA auto highlighter will not highlight over existing conditional formatting. Is there a way to include highlighting over rows with conditional formatting?

  5. This solution (Auto-highlight row and column of selected cell with VBA) works great. But if I am not an idiot, the function of highlighting row and column was before a decade or so a part of menue click in the older versions of Excel. Why are these usefull functions disappearing from Excel in later versions - this is crazy user-unfriendly.

  6. Hi everione, in order to conditionaly format just the selected cell i just replaced the function OR with AND so it works.

    Thanks for the article!!! It helped me a lot.

    Good luck.

  7. Hey, would there be any update on the undo functionality using VBA?
    We're trying to work around on this, please let us know if there are any updates.

  8. Hi,
    How to Highlight ONLY the Cell I Click ?
    Thanks

    1. Hi,

      try this:

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      If Target.Cells.Count > 1 Then Exit Sub
      Application.ScreenUpdating = False

      'Clear the color of all cells
      Cells.Interior.ColorIndex = 0
      ActiveCell.Interior.ColorIndex = 8

      Application.ScreenUpdating = True
      End Sub

  9. Hi all,

    This is very helpful, thanks!
    Is there a way to apply this for multiple selected rows?

  10. Is it possible that my previously highlighted part will not change?

  11. How to hightlight rows only in current region except the header row by VBA?

    Regards

    Buvana

  12. Hi

    Works like a charms. Thank you.

    I was wondering thou - If i want to highlight only certain Column and rows not ENTIRE ... how to do the range

    For instance - highlight Columns from A to BE ; and ROWS only from 3 to 80

    So that the highlights doesnt go entire 1000000 rows ... and columns

    Is it possbile? How to do that? Range Rows Count ... i am clueless.

    Thank you

    Bojan

  13. Hi. I was wondering if this highlight function (for the active row and column of the selected cell) is readily available in your Excel add-on?

    Thanks.

    1. Hello!
      Our Ultimate Suite for Excel does not include this feature. But there are 70+ professional tools with 300+ options, among which you can find useful ones for yourself. You can read more details on this page.

  14. Here is the problem I have - whenever I make changes to the cell and move on, I can't "undo". I have a feeling it's caused by the VBA formatting.

    1. Hi Jason,

      Absolutely so. The VBA code that highlights the active row and column blocks the undo functionality on the sheet. This limitation is explicitly mentioned under Drawbacks.

  15. hi, thanks for this.

    I tried the first VBA but every time I move to a new cell I just get an error popup 'Compile error: Invalid outside procedure'. The spreadsheet is very simple (just a small csv) and not other formatting.

    I'm running Excel form Mac on 2021 apple silicon machine: excel normally works find, though I never use macros apart from this one try. Any suggestions?

    best,
    Scott

    1. Hello!
      VBA on MacOS does not work with worksheet and workbook events, does not call system windows. There are also other differences. Therefore, not all macros will work. Use a virtual machine (Parallels Desktop)

    2. Hi Scott,

      This code (as well as most other codes on our blog) is for Windows. It won't work in Excel for Mac, sorry.

  16. Hey Svetlana.. Hope you're well :)

    I tried highlighting rows and cols with the conditional formatting , it's not working even after pressing f9 key. Could you please suggest some idea. I wanted to highlight my rows and cols only with the help of conditional formatting

    1. Hi Misha,

      It's hard to say what the problem might be without seeing your worksheet. You can download our practice workbook at the end of this post and examine the rule on the sheet named "Conditional formatting". To check how it works without VBA, delete the code from the module of the above-mentioned sheet. I've just re-tested it, and the F9 key works fine on my machine with Excel 365 installed.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)