David has attended:
Excel Advanced course
Excel VBA Introduction course
Excel VBA Intermediate course
Excel VBA Advanced course
Highlight selected row AND return to previous format
Good Morning,
I have found the following code which highlights the entire row of the activecell:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.Pattern = xlNone
Selection.EntireRow.Interior.Color = vbRed
End Sub
However, I am having a problem in that it replaces the formatting of all cells in the row and when the row changes it does not return to its previous formatting.
Is there a way around this?
RE: Highlight selected row AND return to previous format
Hi David,
Thank you for the forum question.
It is possible but the question is, if it makes sense, because you will need a lot of hard work.
When you write bring it back to old formatting, you must talk about colours. The line "Cells.Interior.Pattern = xlNone" removes all colours from all cells, but it doesn't change number formatting.
You will need to store all the colour codes and all cell references for all coloured cells the whole sheet in an Array. It is the only way Excel can "remember" the old formatting.
Then you will need to change the worksheet change event. You will have to tell Excel what you want for all the rows you do not want to highlight.
(if not intersect(target, Activecell.entirerow) then)
You will have to loop through all cells in the Array which are not in the ActiveCell.Entirerow and then call the colour codes from the Array.
I hope this makes sense and can guide you in the right direction.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
