row/column highlight

[jammoca]

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 ??

 

[venkat1926]

try this event 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]

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]

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]

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

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]

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]

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]

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]

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]

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.