i want to make all the rows in the second columns to be a drop down list. where the user click on the cell and select any existing name.

我想让第二列中的所有行成为下拉列表。用户单击单元格并选择任何现有名称。

how to do it ?

怎么做？

when i run this code it display an error:

当我运行此代码时，它显示一个错误：

invalid or unqualified reference

无效或不合格的参考

code:

Sub test()
Dim i As Integer
Dim OfficerList(4) As String

OfficerList(0) = "test1"
OfficerList(1) = "test2"
OfficerList(2) = "test3"
OfficerList(3) = "test4"
OfficerList(4) = "test5"

For i = Range("B5000").End(xlUp).Row To 2
  Select Case VBA.CDate(Cells(i, 2))
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
         Operator:=xlBetween, Formula1:=Join(OfficerList, ",")
  End Select
Next


For i = Range("C5000").End(xlUp).Row To 2 Step -1 'Range upto 5000, chnge this as per your requirment'

    Select Case VBA.CDate(Cells(i, 3))

    Case Is < VBA.Date()
        Cells(i, 3).Interior.Color = vbGreen

    Case Is = VBA.Date()
        Cells(i, 3).Interior.Color = vbYellow

    Case Is > VBA.Date()
        Cells(i, 3).Interior.Color = vbRed

   End Select
Next
End Sub

2 个解决方案

#1

This doesn't seem like the right place for Select Case: try this instead for your first loop

这似乎不是Select Case的正确位置：尝试使用此代替第一个循环

For i = Range("B5000").End(xlUp).Row To 2 Step -1
    With Cells(i, 2).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(OfficerList, ",")
    End With
Next i

Instead of Select Case, I just used With and made sure to specify that you were changing the cell's validation entry, and I changed the loop to include Step -1 (like your second loop).

而不是Select Case，我只使用With并确保指定您正在更改单元格的验证条目，并且我将循环更改为包括Step -1（就像您的第二个循环）。

#2

Have a look at this. I've combined your loop as well. Not sure why you're running it backwards but I've left it doing so

看看这个。我也把你的循环结合起来了。不知道为什么你要向后运行它，但我已经离开了它

Sub test()
    Dim i As Long
    Dim OfficerList(4) As String

    OfficerList(0) = "test1"
    OfficerList(1) = "test2"
    OfficerList(2) = "test3"
    OfficerList(3) = "test4"
    OfficerList(4) = "test5"
    ' Update this with your sheet
    With Sheet1
        For i = .Range("B5000").End(xlUp).Row To 2 Step -1
          With .Cells(i, 2).Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                 Operator:=xlBetween, Formula1:=Join(OfficerList, ",")
          End With

          Select Case VBA.CDate(.Cells(i, 3))
            Case Is < VBA.Date()
                .Cells(i, 3).Interior.Color = vbGreen
            Case Is = VBA.Date()
                .Cells(i, 3).Interior.Color = vbYellow
            Case Is > VBA.Date()
                .Cells(i, 3).Interior.Color = vbRed
           End Select
        Next i
    End With
End Sub

#1