📨 Have you signed up to the Forum's new Email Digest yet? Get a selection of trending threads sent straight to your inbox daily, weekly or monthly!

Microsoft Excel Help Required ... please !

WLM21
WLM21 Posts: 1,688
Part of the Furniture 1,000 Posts Name Dropper Combo Breaker
Forumite
Is there any way to highlight the row (or indeed the column) of a cell in use.

All I get at the moment is a very small blue highlight over the row number (and column letter) This is not very clear, especially when completing several columns, many quite a distance from the row name.

Yes, I could use the freeze or window commands, but if the whole row could be highlighted, it would be much easier.

Is this possible please ?

Comments

  • Le_Kirk
    Le_Kirk Posts: 27,433
    Part of the Furniture 10,000 Posts Photogenic Name Dropper
    Forumite
    I don't know of an easy automatic way to do what you want but if you click on the row or column rather than the cell it highlights the whole row or column. This is no good if you are trying to enter data. Whichever cell you are "in" is shown to the left of the formula bar. If you are into vba (visual basic) there are programs that you can use, see the following link: -

    https://msdn.microsoft.com/en-us/library/office/ff820716.aspx?f=255&MSPPError=-2147217396
  • WLM21
    WLM21 Posts: 1,688
    Part of the Furniture 1,000 Posts Name Dropper Combo Breaker
    Forumite
    Thanks for the advice.

    I have an old version of Excel and it isn't working.

    On the 2nd link provided, it says from 2013 onwards
  • You can right-click on the tab, select View Code, select ThisWorkbook in the Objects folder at the side, and paste the following code:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Rows(Target.Row & ":" & Target.Row).Select
    End Sub


    However, all it does is highlight the row. It doesn't allow you to type anything into one of the cells.

    If a cell is selected, by definition, the row isn't selected as well.
  • djmsemcgrath
    djmsemcgrath Posts: 170
    Part of the Furniture 100 Posts Name Dropper Combo Breaker
    Forumite
    edited 28 January 2016 at 4:31PM
    Actually, scratch that, the following code should give you a decent result:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    With Cells.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    With Range("A" & Target.Row & ":Z" & Target.Row).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
    End With
    With Target.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    End Sub


    On the With Range("A" & Target.Row & ":Z" & Target.Row).Interior line, change Z for whatever your last column is, or change it to something silly like ZZ to create the illusion that the entire row is highlighted.

    The ThemeColor and TintAndShade make the row a grey highlighted colour. If you need it to be a different colour, let me know.

    Highlighting the column instead of the row might be slightly trickier.
  • WLM21
    WLM21 Posts: 1,688
    Part of the Furniture 1,000 Posts Name Dropper Combo Breaker
    Forumite
    You can right-click on the tab, select View Code, select ThisWorkbook in the Objects folder at the side, and paste the following code:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Rows(Target.Row & ":" & Target.Row).Select
    End Sub


    However, all it does is highlight the row. It doesn't allow you to type anything into one of the cells.

    If a cell is selected, by definition, the row isn't selected as well.

    Tried this first

    Highlights row well, but I can only enter data in column A
  • WLM21
    WLM21 Posts: 1,688
    Part of the Furniture 1,000 Posts Name Dropper Combo Breaker
    Forumite
    Actually, scratch that, the following code should give you a decent result:

    Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    With Cells.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    With Range("A" & Target.Row & ":Z" & Target.Row).Interior
    .Pattern = xlSolid
    .PatternColorIndex = xlAutomatic
    .ThemeColor = xlThemeColorDark1
    .TintAndShade = -0.249977111117893
    .PatternTintAndShade = 0
    End With
    With Target.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
    End Sub


    On the With Range("A" & Target.Row & ":Z" & Target.Row).Interior line, change Z for whatever your last column is, or change it to something silly like ZZ to create the illusion that the entire row is highlighted.

    The ThemeColor and TintAndShade make the row a grey highlighted colour. If you need it to be a different colour, let me know.

    Highlighting the column instead of the row might be slightly trickier.

    Tried this too .. pretty good

    If I click on the row number the whole row is highlighted. I can then move along it and click on a cell to enter data. The highlight is then lost though.
  • How do you mean? Purposely, the code highlights all of the cells (between A-Z) in the row, except for the cell you clicked on, which has No Fill, so that it's easier to see which one you're on.

    If you didn't want that, and you wanted the cell you click on to keep the highlight, just remove these lines from the bottom of that code:

    With Target.Interior
    .Pattern = xlNone
    .TintAndShade = 0
    .PatternTintAndShade = 0
    End With
  • Is everything working as you wanted now?
  • Stompa
    Stompa Posts: 8,424
    Part of the Furniture 1,000 Posts Name Dropper
    Forumite
This discussion has been closed.
★ ★ ★ Meet your Ambassadors

🚀 Getting Started

Hi new member!

Our Getting Started Guide will help you get the most out of the Forum

Categories

  • All Categories
  • 355.9K Banking & Borrowing
  • 255K Reduce Debt & Boost Income
  • 456.2K Spending & Discounts
  • 248.5K Work, Benefits & Business
  • 606.2K Mortgages, Homes & Bills
  • 179.1K Life & Family
  • 264K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16.2K Discuss & Feedback
  • 37.7K Read-Only Boards
Quick Search

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.