Macro for auto run

[ahamed]

Hi

Can any one help me to develop a code to run the following macro on each click on the Cell

Sub findrow()
Range("C7") = ActiveCell.Row()
End Sub

I want above macro to find the row number when ever I click on the cell rather each time I make macro to run to find out it ...

I would appropriate if you can post your code & give me little explanation on your code as am pretty new to macro.

Thanks

 

[Peter_SSs]

Try this. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Select cell(s)

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    Range("C7").Value = ActiveCell.Row<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[ahamed]

hi Peter_SSs!!

It is working & Thanks a lot for your kind and quick posting. I had even in my mind that ByVal would come here into play. I have no knowledge of what is ByVal & how it is to be used. Appreciated if you can refer me any site to learn more.

Can you also help me to develop a code

to find & give me the row number only withing defined range.

(example - give me the row number if I click the mouse on the cell within the range of B2:E7)

Advance thnx

 

[Peter_SSs]

For learning, you could try a couple of these or consider some MrExcel products/resources

For your code you could try something like this.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">If</SPAN> Intersect(ActiveCell, Range("B2:E7")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        Range("C7").ClearContents<br>    <SPAN style="color:#00007F">Else</SPAN><br>        Range("C7").Value = ActiveCell.Row<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[ahamed]

Hi Peter

Sorry for being late. Really its working and thank for your kind help and the references.

 

[ahamed]

Hi Peter

Hope you can help me one more time. My excel get slow When I run the macro you have sent me last. Even it take little time to go one cell to another from key board.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Range("B2:E7")) Is Nothing Then
Range("C7").ClearContents
Else
Range("C7").Value = ActiveCell.Row
End If
End Sub

Pls give your comment ..

Thanx

 

[Peter_SSs]

My excel get slow When I run the macro you have sent me last. Even it take little time to go one cell to another from key board.

Pls give your comment ..

I can't see that this code should slow your sheet. There are so few lines of code to process and the sheet simply enters or clears a value in C7. You don't 'run' the macro as such, if you have entered in the correct place it should just automatically be triggered when the selection on the sheet is changed. In my test sheet I can navigate around cells by mouse or keyboard as fast as on any aother sheet.

Do you have any other code in the workbook as well as this code?

Just as a test, start a brand new workbook and try the code in that and see how the speed is.