Microsoft Excel Help Required ... please !

WLM21

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 ?

googler

Maybe

http://www.extendoffice.com/documents/excel/1494-excel-highlight-active-row-and-column.html

Le_Kirk

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

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

djmsemcgrath

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

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

damianjmcgrath wrote: »

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

damianjmcgrath wrote: »

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.

djmsemcgrath

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

djmsemcgrath

Is everything working as you wanted now?

Stompa

One possibility:

https://www.youtube.com/watch?v=bGjqDGF7xaM