Selection Change Event

[Shay A]

Hi,
I found this code to highlight current row and column within the current region/
Is it possible to mend it so it doesn't override previous formatting?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  ' Clear the color of all the cells
  Cells.Interior.ColorIndex = 0
  If IsEmpty(Target) Or Target.Cells.Count > 1 Then Exit Sub
  Application.ScreenUpdating = False
  With ActiveCell
  ' Highlight the row and column that contain the active cell, within the current region
  Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Interior.ColorIndex = 8
  Range(Cells(.CurrentRegion.Row, .Column), Cells(.CurrentRegion.Rows.Count + .CurrentRegion.Row - 1, .Column)).Interior.ColorIndex = 8
  End With
  Application.ScreenUpdating = True
End Sub

and this is the link:

https://msdn.microsoft.com/VBA/Excel-VBA/articles/highlight-the-active-cell-row-or-column


TY

 

[Marc L]

Hi !

Nope ! As written in the link …
​

 

[Marc L]

Better than this bad link is to warm a pair of neurons
in order to not alter any formatting, just selecting
expected column and row … Totally useless but
nothing to amend, just think and create a good code ! (4 codelines …)
​

 

[Shay A]

Thank you, I would love it if you could help me with a good code.

Shay

 

[Marc L]

In the SelectionChange event procedure,
desactivate first events (see in VBA inner help EnableEvents property),
Select the Column or the Row (or both via Union) of the Target
then Activate the Target and finally reactivate events …

Easy but as yet written totally useless and may be a mess for users !
​

 

[Shay A]

So what are you suggesting?

 

[Marc L]

As written, first read VBA inner help for each statement given
in my previous post then follow directions when writing your code …
​