Forum Discussion

Michael Dodd's avatar
Michael Dodd
Copper Contributor
Apr 10, 2018
Solved

Hilighting Rows on rollover

I would like to learn how to format rows so that they appear in highlight as the cursor moves over them (or clicks on the row).   The key is that I don't want it to stay highlighted as I move to the next record.  Is there a way to do this?

excel
  • Thomas Urtis's avatar
    Thomas Urtis
    Apr 10, 2018

    If by "format rows" you mean the active cell's entire row, from columns A to XFD, shall be highlighted upon selection, you can right-click your worksheet tab, left click View Code, and paste the following procedure into the large white area that is the worksheet module. To return to the worksheet, press Alt+Q.

     

    This code will nullify any pre-existing interior celll colors but will leave Conditional Formatted cells alone. Conversely, you can do this with CF to leave all pre-existing interior color formats intact; if that is what you want instead, please post back.

     

    It will also nullify your Undo stack.

     

    If you have not already done so, save as your workbook for the macro-enabled extension format .xlsm.

     

    Here's the code:

     

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.EntireRow.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub

3 Replies

Sort By
  • Thomas Urtis's avatar
    Thomas Urtis
    Copper Contributor
    Apr 10, 2018

    If by "format rows" you mean the active cell's entire row, from columns A to XFD, shall be highlighted upon selection, you can right-click your worksheet tab, left click View Code, and paste the following procedure into the large white area that is the worksheet module. To return to the worksheet, press Alt+Q.

     

    This code will nullify any pre-existing interior celll colors but will leave Conditional Formatted cells alone. Conversely, you can do this with CF to leave all pre-existing interior color formats intact; if that is what you want instead, please post back.

     

    It will also nullify your Undo stack.

     

    If you have not already done so, save as your workbook for the macro-enabled extension format .xlsm.

     

    Here's the code:

     

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
Target.EntireRow.Interior.Color = vbCyan
Application.ScreenUpdating = True
End Sub
    • Michael Dodd's avatar
      Michael Dodd
      Copper Contributor
      Apr 10, 2018

      Thanks so much Thomas!  It worked.  I feel like an official coder.   Ahem, well almost

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    Apr 10, 2018

    Hi Michael,

     

    This is can only be done with some lines of code as follows:

     

    Rows & Columns

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

     

    Rows Only

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

     

    Columns Only

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

     

    These codes are stored in worksheets code scope.

    Please find the attached file to test them.

Resources