27 thoughts on “Highlight row and column of active cell”

1. Hi Tushar,

   I created a tool that moves around two arrows instead of using CF or changing cell formatting, see:

   http://www.jkp-ads.com/download.asp#FollowCellPointer

2. Hi,

   I remember using Quattro Pro in the old days. This was standard.
   Excel should just have a setting to look after this.

   Cheers,

   Bob

3. Regarding my previous comment about disabled Copy/Paste: the simple code for one row does work =CELL(“row”)=ROW(). The culprit was the code. I had Application.Calculate while you had Target.Calculate. After making the code change, Copy/Paste became functional again. I guess that Application.Calculate wiped off the clipboard.

4. If, and I do know it is a big if, but if there are no manually colored cells on the worksheet, then this simple SelectChange event code can be used to highlight the cells similar to your “Multiple cells” method…

   Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   Cells.Interior.ColorIndex = 0
   Union(Target.EntireColumn, Target.EntireRow).Interior.ColorIndex = 36
   End Sub

   Here I have used a yellow highlight color (ColorIndex 36), but you can change the as desired.

5. using parallels for mac, and the cf doesn’t update with f9 (on the mac I have to hit fn+F9), nor does it update with vba. I have to select another sheet and then return to the sheet with the conditional formatting in order for it to update. FYI.

6. Hi Justin,

   Thanks for the heads-up about the Mac even if it is not obvious why. Using Parallels should result in a Windows VM and that should result in more Windows-like behavior. But, I guess oddities will remain when working with virtualization. ;-)

7. I do not know if these conditional formatting formulas are not highlighting the correct column because i have hidden columns; But the formula only highlights the correct row I’m on and not the correct column. It will highlight maybe the column two or more rows down. Do you maybe have any suggestions for this problem?

8. Great tip!
   The Higlighting a cell in another collumn was just what I was looking for. Now I would love if it also triggered the Show/hide comment for the highlighted cell

9. Same here, the formula does not recalculate and highlight properly unless I leave the sheet and return – hitting f9 or setting up the VBA does not do the trick (windows 2008 with Office Professional Plus 2010)

10. Seems very drastic, but I found a workaround to the problem of recalculation not working properly unless the worksheet was changed … I basically change the worksheet programmatically while disabling the display of this change until I’m back on the desired worksheet for which I want recalculation to take place. I’m hoping that you can find a better solution. Here’s the code I use to get the worksheet to show the highlighting properly:

    Application.ScreenUpdating = False
    Application.Sheets(“Sheet1”).Activate
    Application.Sheets(Target.Worksheet.Index).Activate
    Application.ScreenUpdating = True

11. For some reason this article’s methods worked fine with a different worksheet in the same workbook, so I’m not sure why it didn’t work with the first worksheet (which had no formulas).

12. The VBA method doesn’t work in Excel 2007. I have to keep pressing F9. Macros are enabled, of course.

13. Suzette: I realize it’s been a few months but I just saw your comment. The only thing I can think of is that the column reference in the conditional formatting formula is off by 2 or 3 columns. You may want to check that.

    John: Not quite sure what to write. There’s nothing in the solution that relies on the absence or presence of formulas.

    Mike: I just downloaded the file and checked in Excel 2007. Works just fine for me. So, not sure what the issue is in the setup you are using.

14. Mike: Have you switched off the auto-calculation option?

15. This has been a great tool for me, thank you! However I share a workbook with someone who would rather not have the automatic highlighting. Any suggestions on how to set the highlighting to a toggle?

    Also, is it possible for this to not overlay the existing cell color? For example, cell B10 has a yellow fill color already. How would I go about setting this to only fill column B and Row 10, except for B10?

    Thanks again!

16. To highlight only the selected cell: the instructions are the same as for “Highlight cell in specific column and same row as selected cell,” except:
    The formula is =AND(CELL(“col”)=CELL(“col”,A1),CELL(“row”)=CELL(“row”,A1))

    ————

    One disadvantage to this highlighting business: the highlighting will show up when you print the worksheet. :-(
    But there is a way to avoid this: select a cell that’s OUTSIDE of the print area before you print. (If you’re highlighting entire rows and columns, select a row and column outside of the print area.)

17. Firstly thanks for the awesome resource and the example files.
    Is there a way for this code to work from the personal macro file ? so that every excel sheet I use does not have to be converted to a macro enabled worksheet ?

18. If you have Manual calculation mode then you may notice that the colour does not update cleanly automatically. I like manual mode for the workbook I use as it has a lot of formulas in it.

    So a better workaround that the sheet change above is below. For some reason the screen updating false to true toggle is enough to instantly update the screen and you’ll see the conditional format instantly instead of a delay.

    Option Explicit

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Target.Calculate ‘this will auto update the conditional formatting on this sheet to do the highlighting

    Application.ScreenUpdating = False ‘THIS AUTO UPDATES THE SCREEN SO THAT THE CONDITIONAL FORMAT MOVES STRAIGHT AWAY EVEN IN MANUAL CALCULATION MODE

    Application.ScreenUpdating = True ‘THIS AUTO UPDATES THE SCREEN SO THAT THE CONDITIONAL FORMAT MOVES STRAIGHT AWAY EVEN IN MANUAL CALCULATION MODE

    End Sub

19. We can select the row and columns instead of changing the color of cells.In this way it will not override the previous formatting.

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.EnableEvents = False
    Range(Target.EntireRow.Address & “,” & Target.EntireColumn.Address).Select
    Target.Activate
    Application.EnableEvents = True

    End Sub

20. Thank you so much! This really helped me!

21. X Trasformer

    Hi, I’ve tried your vba code in my Excel 2010 but doesn’t work!
    Can you tell me the code to select the row and column of the active cell, instead of changing the color of cells, so that it will not override the previous formatting?

    Thanks a lot in advance!

22. In thisworkbook codemodule:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Application
.EnableEvents = False
.Union(Sh.Rows(Target.Row), Sh.Columns(Target.Column)).Select
.EnableEvents = True
End With
End Sub

23. X sbn

    Thanks a lot, I’have put the code in thisworkbook codemodule and it selects row and column but it selects as active the first cell on the right (the cell of the first column), so it is impossible to write in other cells!
    How can I highlight row and column of the active cell without overriding the previous formatting and mantaining copy, paste and redo?
    Thanks in advance!

24. Use this to select the row of the active cell. Don’t know how to do both row and column

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Application.EnableEvents = False
    Range(Target.EntireRow.Address).Select
    Target.Activate
    Application.EnableEvents = True

    End Sub

25. Just:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
With Application
.EnableEvents = False
.Union(Sh.Rows(Target.Row), Sh.Columns(Target.Column)).Select
Target.Activate
.EnableEvents = True
End With
End Sub

26. @Transformer: Now that is a great idea.

27. Except when you got a merged cell in the path of the pseudo highlight (; But otherwise very cool.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.