row/column highlight

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
When I click on a cell, is there a way to have the cells in the column and row that intersect with that cell highlighted or greyed-out automatically .. to highlight the intersection itself ??
 

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.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
try this event code

Code:
private Sub Worksheet_SelectionChange(ByVal Target As Range)


Rows(Target.Row).Interior.ColorIndex = 3
Columns(Target.Column).Interior.ColorIndex = 6

End Sub

rightlcick the sheet tab and click view code and copy the above code.

goto the sheet and select any cell and see what happens

modify code to suit you;.
 

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
Thanks so much for that. It works well, but is there an adjustment we can make to the code so that each highlighted column and row becomes unhighlighted when I leave that cell and press another cell ( and of course, the column and row for that cell would then in turn become highlighted ???
 

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
I am still unable to prevent the rows and columns from going back to normal colour when I click into another cell. Any changes to the code ?
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
when you go to another cell you want the earliler coloring to be removed. is it correct. in that case use this modified event code

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Cells.Interior.ColorIndex = xlNone
Rows(Target.Row).Interior.ColorIndex = 3
Columns(Target.Column).Interior.ColorIndex = 6

End sub

do you get what you want
 

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
That's fantastic ... worked a treat, however, when I copy that code into several sheets within the one workbook, I get a debug message on all sheets except the first one I copied it into. Any clues ?
 

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
This is the error message I receive :

Run-Time error '1004':
Unable to set the ColorIndex property of the Interior Class

and if I press the Debug option, it takes me to the following line of the Code provided by venkat1926 ...

cells.Interior.ColorIndex=xlNone

any clues ??
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
where did you copy the macro

rightclilck EACH sheet tab(name at the bottom) and click view code and copy the macro.

now go to each sheet select any cell and see.

It seems to work for me

any problem post details
greetings
venkat
 

jammoca

Banned
Joined
Nov 6, 2002
Messages
1,100
I found the reason for the error message ... the code seems to have a problem when the sheet/s is/are protected.

any clues to remedy ??
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
in the macro add a line at the beginning to unprotext and at the end another code to protect back. If to unprotect you need a password it will be more complicated.
 

Forum statistics

Threads
1,181,102
Messages
5,928,063
Members
436,586
Latest member
latintxn

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