VBA: Write value into a specified cell when the active cell is within a range

[nolmo]

Hi everybody,


I have just started learning Visual Basic so I would like to ask your help because I couldn't find a tutorial that solves my problem. I would like to write a macro that write a specified number into a specified cell if the active cell is within a range. I would like to use this number as a trigger to control other things such as conditional formatting.


I give you a concrete example:


If I click anywhere within A1 and G1 then I would like the macro to write "1" into J1 (for instance). But if I click anywhere between A2 and G2 then I would like to get number "2" in J1 and so on. I want J1 to be left blank if I click outside of these ranges (A1:G1, A2:G2, etc.).


I am not a native English speaker so if something is not clear then I try to explain in other words.


Thank you for your help in advance guys.

 

[JoeMo]

Welcome to the board!

Install the code below in your worksheet.
To install the code:
1. Right-click the worksheet tab you want to apply it to and choose 'View Code'. This will open the VBE window.
2. Copy the code below from your browser window and paste it into the white space in the VBE window.
3. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
4. Make sure you have enabled macros whenever you open the file or the code will not run.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Columns("A:G")) Is Nothing Then
    Range("J1").Value = Target.Row
Else
    Range("J1").ClearContents
End If
End Sub

 

[nolmo]

Wow!


Thanks for the quick answer JoeMo!


I have tried your code and it took me much closer to the solution but it is not exactly what I want. The first example was just a fictional one but my case is not that simple. It is my bad. I have a range between D7 and M14 and I want the macros to take place only in this range the way I described above. As you see, this range contains 8 rows from column D to M. I would like to get number 1 in G16 if I click anywhere within D7 and M7 and number 2 (in G16 again) if I click anywhere within D8 and M8 and so on. When I reach D14:M14 number 8 should be in G16. But when I click anywhere else outside of D7:M14 then I would like G16 to be blank.


I hope I could give a better description.

 

[JoeMo]

Wow!


Thanks for the quick answer JoeMo!


I have tried your code and it took me much closer to the solution but it is not exactly what I want. The first example was just a fictional one but my case is not that simple. It is my bad. I have a range between D7 and M14 and I want the macros to take place only in this range the way I described above. As you see, this range contains 8 rows from column D to M. I would like to get number 1 in G16 if I click anywhere within D7 and M7 and number 2 (in G16 again) if I click anywhere within D8 and M8 and so on. When I reach D14:M14 number 8 should be in G16. But when I click anywhere else outside of D7:M14 then I would like G16 to be blank.


I hope I could give a better description.

Good idea to provide that information in your initial post.
Replace the code I posted earlier with this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("D7:M14")) Is Nothing Then
    Range("G16").Value = Target.Row - Range("D7:M14").Cells(1, 1).Row + 1
Else
    Range("G16").ClearContents
End If
End Sub

 

[nolmo]

Works perfectly. Thanks JoeMo. You are awesome.