Populate a range by clicking another cell

[fatnhappy]

I have the below code and it works fine, but I am looking to do 4 things with it and am a little lost

1. Make the intersect Target Range B3:B64 AND E3:E64 AND H3:H64 AND K3:K64, instead of all cells in all the columns of the range
2. consolidate the code by making the sections a range instead of seperate if statements to place target value(so first range would be Q12:Q17)
3. Add additional ranges in addition to Q12:Q17 (i.e. Q24:Q29, Q34:Q39)
4. Anytime I click on a cell in any of the defined ranges in column Q, change the contents of the cell to "Pick a Golfer"

Hours of research have yielded no results!

THanks in Advance

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("B3:K64")) Is Nothing Then Exit Sub

If Range("q12").Value = "Pick a Golfer" Then
Range("q12").Value = Target.Value
Else:
    If Range("q13") = "Pick a Golfer" Then
    Range("q13").Value = Target.Value
Else:
    If Range("q14") = "Pick a Golfer" Then
    Range("q14").Value = Target.Value
Else:
    If Range("q15") = "Pick a Golfer" Then
    Range("q15").Value = Target.Value
Else:
    If Range("q16") = "Pick a Golfer" Then
    Range("q16").Value = Target.Value
Else:
    If Range("q17") = "Pick a Golfer" Then
    Range("q17").Value = Target.Value

Else:  Exit Sub
End If
End If
End If
End If
End If
End If




End Sub

 

[rollis13]

If I correctly understood your request try my version:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim found As Range
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("B3:B64, E3:E64, H3:H64, K3:K64")) Is Nothing Then
        Set found = Range("Q12:Q17, Q24:Q29, Q34:Q39").Find(What:="Pick a Golfer", LookIn:=xlValues, LookAt:=xlWhole)
        If Not found Is Nothing Then found.Value = Target.Value
    End If
    If Not Intersect(Target, Range("Q12:Q17, Q24:Q29, Q34:Q39")) Is Nothing Then
        Target.Value = "Pick a Golfer"
    End If
End Sub

 

[fatnhappy]

If I correctly understood your request try my version:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim found As Range
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("B3:B64, E3:E64, H3:H64, K3:K64")) Is Nothing Then
        Set found = Range("Q12:Q17, Q24:Q29, Q34:Q39").Find(What:="Pick a Golfer", LookIn:=xlValues, LookAt:=xlWhole)
        If Not found Is Nothing Then found.Value = Target.Value
    End If
    If Not Intersect(Target, Range("Q12:Q17, Q24:Q29, Q34:Q39")) Is Nothing Then
        Target.Value = "Pick a Golfer"
    End If
End Sub

This worked EXACTLY as I planned.....such a nice streamlined code. I did make a minor change because it wasn't starting at Q12, so I changed it to Q11 and then worked in order. Not sure why but my fix worked. Thanks so much

 

[rollis13]

Thanks for the positive feedback, glad having been of some help. As I have no idea on how your sheet layout is organized all I can say is that for Excel Q12 is Q12 and Q11 is Q11, so ... but if it works.
By the way, you probably need to mark this thread as [Solved].