Highlight column and row of active cell but retaining the previous formats

[stelios]

I have found this code by searching witch is perfect

If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Cells.Interior.ColorIndex = 0
With Target 'With the cell that was selected,
    .EntireRow.Interior.ColorIndex = 8
    .EntireColumn.Interior.ColorIndex = 8
End With

the point is that i want to retain the previous formating of cells when i select another cell.
and also i would like to create a switch to turn the Worksheet_SelectionChange event on and off. the tricky part is here i guess, becouse i would like when turned off to retain the previous formating also.
Any ideas?

Thanks for your help and precious time in advance.

Stelios

 

[Hui]

Stelios
As you've found there is no way to remember the previous or historical formatting
However you can use Conditional Formatting to do what you want all with minimal VBA
Have a read of : http://chandoo.org/wp/2012/07/11/highlight-row-column-of-selected-cell-using-vba/

 

[stelios]

Thanks for your reply.
This was most helpfull!!!

 

[r_2]

hi can any one help me on this through conditional formatting, new rule

if i write=ROW(A1)=selRow
=COLUMN(A1)=selCol

it converts into
=COLUMN(A1)=selCol
=ROW(XEV1)=selrow

& didn't get any result....

Pls suggest why iam getting error

 

[r_2]

including showing error 424

 

[NARAYANK991]

Hi ,

Please post your workbook , since otherwise only general answers can be given , which may or may not be helpful.

1. Have you created the named ranges selRow and selCol ?

2. If you have used the Option Explicit statement in your VBA Editor , have you declared the variables selRow and selCol ?

The proper usage in your VBA code would be [selRow] and [selCol] , since these are worksheet named ranges , and not VBA code variables.

Narayan