How to populate a cell[s] by clicking on another cell

liemaj

New Member
Hi all,

1st post here. I want to cells to populate with "Y" when someone clicks on another cell in a worksheet. Is this possible within excel? If so, could someone assist.

Example, someone clicks on cell A1 then cells A2, A3, A4, A5 etc. are populated with Y.

Thanks.
 

JoeMo

MrExcel MVP
Hi all,

1st post here. I want to cells to populate with "Y" when someone clicks on another cell in a worksheet. Is this possible within excel? If so, could someone assist.

Example, someone clicks on cell A1 then cells A2, A3, A4, A5 etc. are populated with Y.

Thanks.
Try this sheet event code.

To install sheet 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.
Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Range("A2:A5").Value = "Y"
End If
End Sub
 

liemaj

New Member
Try this sheet event code.

To install sheet 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.
Rich (BB code):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
    Range("A2:A5").Value = "Y"
End If
End Sub
This works, thank you very much!
 

liemaj

New Member
You are welcome - thanks for the reply.
Hello! Me again, so i've been playing around with your code. I was hoping I could easily figure out how to do this, but it's proving harder than I envisaged.

So I basically want to be able to replicate the code above for multiple columns in a worksheet i.e. if I click A1, populate A2, A3, A4 etc with Y, if I click B1 populate B2, B3, B4 etc.

Apologies if this is basic, but i am very, very new to VBE.
 

Joe4

MrExcel MVP, Junior Admin
The following code will update rows 2-5 in any column with "Y" whenever a single column in row 1 is selected:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Rows("1:1")) Is Nothing Then
    Range(Cells(2, Target.Column), Cells(5, Target.Column)).Value = "Y"
End If
End Sub
 

liemaj

New Member
The following code will update rows 2-5 in any column with "Y" whenever a single column in row 1 is selected:
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Rows("1:1")) Is Nothing Then
    Range(Cells(2, Target.Column), Cells(5, Target.Column)).Value = "Y"
End If
End Sub
Yup, this works! Thanks for the quick response.
 

Joe4

MrExcel MVP, Junior Admin
You are welcome.
Glad us "Joes" were able to help!;)
 
Last edited:

Some videos you may like

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Syntax errors
    Good Morning, Trying to compile a workbook, I keep getting a few errors. Here are the first two: [code=rich]Syntax Error: Function...
Top