VBA to create a drop-down list of say, red, green, blue.

[jkalpus]

Here's the details: Let's say I have a drop down list in 1400 rows of column Q. The drop-down list is populated by a named range, COLORS. I was a drop-down list in every cell of the 1400 rows of Col Q. Easy enough. Here's the rub: The first item in the drop-down list is RED. If a user selects RED in A1, A3, A5, etc. (all the odd-numbered rows) then I want A2, A4, A6, etc. (all the even numbered rows) to be equal to the odd-numbered row above.

To recap, I want a drop-down list in all 1400 rows of col Q, and I also want the even-numbered rows to echo the row above if a user select the first item in the drop-down list, RED. Even IF they select RED in A1, for example, i STILL want/need a drop-down list in A2, A4, etc.


Thanks in advance! jk

 

[Yongle]

Only VBA can do what you want
- the cell value can either be the resut of a formula or the result of selection (from dropdown) but not both

Need more info:
When user selects "RED" Q1 is it only Q2 that changes to RED"?
- if not which other cells changes

If Q1 and Q2 both contain "RED" and user selects "BLUE" in A3 is it only A4 that changes to "RED"?
- if not which other cells changes

If Q2 is already "RED" (because Q1 is RED) what happens if user selects "BLUE" in Q2

Please be precise

 

[Fluff]

If I understand you correctly, try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
   If Target.Column = 17 And Application.IsOdd(Target.Row) Then
      Target.Offset(1).Value = Target.Value
   End If
Application.EnableEvents = True
End Sub

This needs to go in the sheet module.
Whenever you change col Q on an odd numbered row the cell below will mirror that value

 

[Fazza]

This reminds me of https://www.mrexcel.com/forum/excel...tem-drop-down-list-using-vba.html#post5058088

It seems like a clearer understanding of the overall situation might lead to a different & better solution. Perhaps a re-design of the approach, if you're interested.