Highlight Row Column of Selected Cell using VBA

[Mortadella]

Hi!


I got this suggestion the other day and am working on a large spreadsheet range A2:Q695 which I am using to reconcile various items against each other and the ability to highlight the row and column I am in would be terrific.


I followed the directions very carefully but after all is said and done once I had completed the conditional formatting (which I set up as =ROW(A2)=selRow and similarly =COLUMN(A2)=selCol) when I got onto the spreadsheet I immediately got an error 424 Object Required message.


I reviewed everything and can't seem to figure out where I'm off base - any help would be greatly appreciated

 

[bobhc]

Good day Montadella


If you put this into a cell it will tell you the last cell you worked on and keeps this information for when you re-open your work book so you will always know where you are in the spreadsheet.

`

=CELL("address")

 

[bobhc]

This link is from he chandoo site, amazing little box at top right works similar to a search..........but it is


http://chandoo.org/wp/2012/07/11/highlight-row-column-of-selected-cell-using-vba/

 

[Mortadella]

Is this what you are telling me to do?


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[selRow] = Target.Row

[SelCol] = Target.Column

=CELL("address")

End Sub


Because this gives me a syntax error

 

[bobhc]

I am telling you nothing, just trying to point in the right direction

I have re-read chandoos e-mail again and the jist of the e-mail is :-

[pre]

•	[selRow] = Target.Row
•	[selCol] = Target.Column
Select the rule type as Use a formula… and use a below rules.
•	=ROW(B4) = selRow
•	Apply formatting
•	Repeat the steps & this time use the rule =COLUMN(B4) = selCol

[/pre]
I can not find any reference to "=Cell("address") in chandoos post,because there is no reference to the CELL function. You are not reading the post, just skimming. For reasons I do not understand you are joining two posts in to one, and both are totally different in what they do.

Chandoos formula highlights the cell,row and column you are working in, the CELL function tells you the last cell you where working in.

Chandoos function/formula is vba, as he quite clearly says.

The cell function is to be placed in a cell, as I hoped I had made clear.

 

[Mortadella]

I'm sorry I'm no macro expert - I thought the article Chandoo sent out made sense but I seem to be missing something. I printed his posting out on three pages and walked step by step through it. It was then I ran into the problem error 424 Object Required.


I posted a query and you responded =CELL("address") and I mistook your comment to be the answer to my problem, so I put it into my routine.


I have two cells that are named selRow and selCol and when I travel through my spreadsheet I can see the numbers chnaging as I move my cursor but despite outlining the range and instituting conditional formating I do not see the highlighting I had hoped to see. My impression was that by selecting the whole range the conditinal formatting would apply to all rows and columns in the range but like I said I don't see any highlighting when I move around.


As I said I'm not an allstar in VBA but I believe I have followed all the instructions and I am not getting the results so if you can help I'd appreciate it. If I knew how to attach the worksheet to this I could show you.


Thanks

 

[Mortadella]

I'm sorry I'm no macro expert - I thought the article Chandoo sent out made sense but I seem to be missing something. I printed his posting out on three pages and walked step by step through it. It was then I ran into the problem error 424 Object Required.


I posted a query and you responded =CELL("address") and I mistook your comment to be the answer to my problem, so I put it into my routine.


I have two cells that are named selRow and selCol and when I travel through my spreadsheet I can see the numbers chnaging as I move my cursor but despite outlining the range and instituting conditional formating I do not see the highlighting I had hoped to see. My impression was that by selecting the whole range the conditinal formatting would apply to all rows and columns in the range but like I said I don't see any highlighting when I move around.


As I said I'm not an allstar in VBA but I believe I have followed all the instructions and I am not getting the results so if you can help I'd appreciate it. If I knew how to attach the worksheet to this I could show you.


Thanks

 

[NARAYANK991]

Hi ,


Can you please follow the steps outlined below , and confirm whether it works for you ?


1. Create two named ranges ; name one selRow , and in the Refers To box , enter the cell address of any unused cell , in any sheet in your workbook ; name the other selCol , and in the Refers To box , enter the cell address of any unused cell , in any sheet in your workbook. This is only to make the names selRow and selCol refer to some physical addresses within your workbook. Since the row and the column can be different , ensure that the two names refer to two different cell addresses.


2. Select any cell on the worksheet where you need the row and column highlighting , and select Conditional Formatting ; select New Rule , select Use a Formula , and type in the following formula in the dialog box :


=ROW()=selRow


Choose any color that you want.


Select Conditional Formatting again ; select New Rule , select Use a Formula , and type in the following formula in the dialog box :


=COLUMN()=selCol


Choose any color that you want.


Both the above rules are for the same cell.


3. With the cursor still in the same cell , click on Conditional Formatting again ; select Manage Rules ; you should see your two rules there. In the Applies To box , delete whatever you see there , and type in :


=$1:$1048576


Do this for both the rules.


What this is doing is applying the same Conditional Formatting to every cell on the worksheet.


4. Go into the VBE ( Visual Basic Editor ) , and double click on the sheet name in the left side of the screen ; in the blank window on the right , paste the following code :

[pre]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[selRow] = Target.Row
[SelCol] = Target.Column
End Sub

[/pre]
You are done.


Now , if you go back to your worksheet , and move the cursor , you should see the highlighting.


Narayan

 

[Lymm]

OOps I tried doing this and obviously didnt get it quite right as my whole sheet changes to the number of the column selected. Luckily I was just testing on a copy of my sheet, I must try harder By the way Narayan thanks for the detailed explanation, I will go back and do it again.

 

[bobhc]

The explanation given by NARAYANK991 is most detailed as you would expect form such an exalted Ninja.

But the Chandoo link I posted gives a pictorial step by step of how to enter the formula including how to copy paste the VBA and if you follow the simple steps you can not get it wrong.

If Mortadella had read this he would not have tried to insert the cell function into the VBA