Row and Column highlight issue

[jammoca]

here's a code I'm using to highlight the row and column that intersect a cell I click into .....

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect "ana"
Cells.Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.ColorIndex = 3
Columns(Target.Column).Interior.ColorIndex = 4
ActiveSheet.Protect
End Sub

However, it's removing any cell-shading that I had on the sheet already, and not returning it.

Is there some addition to the code that will allow me to highlight the intersection of row and column, but NOT kill off any pre-existing cell shading ??

 

[ravishankar]

Hi
Try one or more of these highlights

Rows(Target.Row).font.ColorIndex = 3
Rows(Target.Row).font.bold = true
Rows(Target.Row).font.size = 16


Ravi

 

[jammoca]

didn't seem to work for me ...

 

[njimack]

This add-in may do what you're looking for: http://www.andrewsexceltips.com/cell_spotter.htm

 

[Greg Truby]

Neil, I'll have to check that one out.

Personally, I've been using Chip's rowliner add-in for years, whenever I want to highlight the active column &/or row and have been happy with it.

 

[jammoca]

Both those work great, but I'll be sending this particular spreadsheet to other clients who most likely will not have (nor feel inclined to downloading and unzipping ) the above cell locators in their excel. So I really need to adapt the code I'm currently using. I'm close except for the fact I keep losing any pre-existing shading.

 

[Greg Truby]

In that case, take a look at the MrExcel Hall of Fame Entry by Ivan. Please note that both Chip and Andrew use shapes rather than formatting -- for just the reason you cite -- rolling back formatting changes is a royal PITA.

 

[jammoca]

That's the one, thanks very much for that reference and thanks so much to the guys who put it together