Highlight active row/column in Excel without using VBA?

Question

What I want to achieve is to highlight active row or column. I used VBA solutions but everytime Selection_change event is used I am loosing chance to undo any changes in my worksheet.

Is there a way to somehow highlight active row / column without using VBA?

Solution 1

I don't think it can be done without using VBA, but it can be done without losing your undo history:

In VBA, add the following to your worksheet object:

Public SelectedRow as Integer
Public SelectedCol as Integer

Private Sub Worksheet_SelectionChange(ByVal Target as Range)
    SelectedRow = Target.Row
    SelectedCol = Target.Column
    Application.CalculateFull ''// this forces all formulas to update
End Sub

Create a new VBA module and add the following:

Public function HighlightSelection(ByVal Target as Range) as Boolean
    HighlightSelection = (Target.Row = Sheet1.SelectedRow) Or _
        (Target.Column = Sheet1.SelectedCol)
End Function

Finally, use conditional formatting to highlight cells based on the 'HighlightSelection' formula:

Solution 2

The best you can get is using conditional Formatting.

Create two formula based rules:

1. =ROW()=CELL("row")
2. =COLUMN()=CELL("col")

As shown in:

The only drawback is that every time you select a cell you need to recalculate your sheet. (You can press "F9")

Solution 3

You can temporarily highlight the current row (without changing the selection) by pressing Shift+Space. Current column with Ctrl+Space.

Seems to work in Excel, Google Sheets, OpenOffice Calc, and Gnumeric (all the programs I tried it in). (Thanks to https://productforums.google.com/forum/#!topic/docs/gJh1rLU9IRA for pointing this out)

Unfortunately, not as nice as the formula and macro-based solutions (which worked for me BTW), because the highlighting goes away upon moving the cursor, but it also doesn't require the hassle of setting it up each time, or making a template with it (which I couldn't get to work).

Also, I found you could simplify the conditional formatting formula (for Excel) from the other solutions into a single formula for a single rule as:

=OR(CELL("col")=COLUMN(),CELL("row")=ROW())

Trade off being that, if you did it this way, the highlighted column and row would have to use the same formatting, but that's probably more than adequate for most cases, and is less work. (thanks to https://trumpexcel.com/highlight-active-row-column-excel/ for abbreviated formula)

Solution 4

First of all Thanks! I had just created a solution with highlighting cells, using the Selection_Change and changing a cells content. I did not know it would disable Undo. I found a way to do it by using combining conditional formatting, Cell() and the Selection_Change event. This is how I did it.

• In Cell A1 I put the formula =Cell("row")
• Row 2 is completely empty
• Row 3 contains the headers
• Row 4 and down is the data
• To make the formula in A1 to be updated, the sheet need to recalculate. I can do that with F9, but I created the Selection_Change event with the only code to be executed is Range("A1").Calculate. This way it is done every time the user moves around, and as the Selection_Change is NOT changing any values/formats etc in the sheet, Undo is not disabled.
• Now just enter the conditional formatting to highlight the cells that have the same row as cell A1.
  • Select the whole column B
  • Conditional Formatting, Manage Rules, New Rule, Use a Formula to determine which cells to format
  • Enter this formula: =Row(B1)=$A$1
  • Click Format and select how you want it to be highlighted
  • Ready. Press OK in the popups.

This works for me.

Solution 5

An alternative to Range.Calculate is using ActiveWindow.SmallScroll The only downside is that the screen flickers for a split second after making a new selection. While scrolling manually, you need to make sure the new selection moves out of the screen (window) completely, for it to work. Which is why, in below code, we need to scroll enough to get all visible rows out of the screen view and then scroll back to same position -to force screen refresh for conditional formatting.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ScreenUpdating = False
ActiveWindow.SmallScroll Down:=150 'change these values to total rows displayed on screen
ActiveWindow.SmallScroll Down:=-150 'change these values to total rows displayed on screen
'DoEvents 'unable to use this to remove the screen flicker
ScreenUpdating = True
End Sub

Credits: Rory Archibald https://www.experts-exchange.com/questions/28275889/When-is-excel-conditional-formatting-refreshed.html

View more solutions

Comments

[Mathias]

Based on hstay's answer, you could simplify this by removing the global variables and the HighlightSelection function. All you need to keep is the Application.CalculateFull in the event handler

[Mathias]

Interesting! I did not know that the CELL() function would indicate which row/col were selected

[hstay]

Neither did I until @dick-kusleika pointed it out in an answer to this question stackoverflow.com/questions/21405521/…

[lowak]

Does it have to be limited to one sheet? I mean this part: HighlightSelection = (Target.Row = Sheet1.SelectedRow) Or (Target.Column = Sheet1.SelectedCol)

[Mathias]

I believe so, but if you use hstay's solution, then it should work for all sheets, as long as you force Excel to recalculate on every selection change. I believe you can put the SelectionChange event handler in the workbook VBA code and it will apply to all sheets

[Mathias]

Correction: if you put the event handler in the Workbook object, you have to use Workbook_SheetSelectionChange instead of Worksheet_SelectionChange

[lowak]

Hm... pretty strange... I used formula given by hstay and everything works fine (when pressed F9). I added your code to CalculateFull and works even better. But something is wrong with your function, does not work at all...

[lowak]

Thanks for this formula I mixed it with e.James's code to autoupdate and it's perfect solution. Especially as it is solution for less experienced Excel users. Plus it does not involve too much working in VBA Editor, which looks pretty scary for less experienced users :)

[Nybbe]

When running Calculate any cells that are linked to the clipboard are loosing that. So, effectively, by putting Calculate in Selecte_Change event effectively hinders doing simple Copy and Paste within the sheet. You can easily try it out by selecting a cell, press Ctrl-v, press F9. When pressing the F9, the moving ants around teh cell are gone and it is no longer possible to paste the cell.

[Nybbe]

If I instead do Range("A1").Calculate, I can still do Copy and Paste.

[lowak]

Anyway, even though your function does not work properly you show me the essence of what I needed. Combining your anwser with hstay solution gives me the outcome I needed. Thank you!

[Freddie Fabregas]

It's working for me! This is great. Tip: If you're using a slow computer. You might want to lower the scope or range of the cells you're working on.

[GuidoG]

@hstay I made these rules and pressed F9 but nothing happens. How do I activate this ?

[Fat Monk]

Turns out you can simplify this a lot... by using the answer from @hstay and simply adding a Worksheeet_SelectionChange event containing Range("A1").Calculate (pointing to any empty cell in your worksheet) the sheet will auto-calculate when you change selection and the highlight will therefore move.

[Fat Monk]

Working from @Nybbe's answer: adding a Worksheeet_SelectionChange event containing Range("A1").Calculate (pointing to any empty cell in your worksheet to avoid confusion) the sheet will auto-calculate when you change selection and the highlight will therefore move.

[gwideman]

Gotcha alert! Don't just copy and paste Aaron's formula, because it contains fancy quotes, and doesn't work as a formula in Excel. No error message, just doesn't work.

[Aaron Wallentine]

@gwideman oops! thanks for that, I fixed the formula, removed the fancy quotes. Hopefully it works now.

[MD. RAKIB HASAN]

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.