Row and Column highlight issue

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
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 ??
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

ravishankar

Well-known Member
Joined
Feb 23, 2006
Messages
3,566
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
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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

Banned
Joined
Nov 6, 2002
Messages
1,100
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

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,022
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

Banned
Joined
Nov 6, 2002
Messages
1,100
That's the one, thanks very much for that reference and thanks so much to the guys who put it together
 

Forum statistics

Threads
1,181,730
Messages
5,931,716
Members
436,800
Latest member
abowalid98

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top