Reputation: 1651
I am trying to have the L column in Excel populate a Drop Down List with values (now Table?) in Excel and here is my code. I have manually created a Table called Table1 with some dummy values. I can hardcode the values inside the Table so that's not an issue. I just need to run some conditions and for some specific rows, I need to add a dropdown at the L column in Excel. Any help would be appreciated. Also, is there an easy way to color specific rows in Excel?
Sub button_click()
Set i = Sheets("Sheet1")
Set e = Sheets("Sheet2")
arrColumnNames = i.ListObjects("Table1")
Dim d
Dim j
d = 1
j = 13
Do Until IsEmpty(i.Range("K" & j))
If i.Range("K" & j) = "Y" Then
d = d + 1
e.Rows(d).Columns("A:E").Value = i.Rows(j).Columns("A:E").Value
i.Rows(j).Columns("L") = arrColumnNames
End If
j = j + 1
Loop
End Sub
Upvotes: 0
Views: 2325
Reputation: 574
About coloring a row Interior.ColorIndex can be used.
Sub colorIt()
Dim rng As Range
Set rng = ThisWorkbook.ActiveSheet.Rows(2)
rng.Interior.ColorIndex = 6
End Sub
Upvotes: 0
Reputation: 91
This is another way to add a drop-down list to a table.
Public Sub AddDropdownListColumnToTable()
Dim sht As Worksheet
Dim colCount As Long
Set sht = ThisWorkbook.Worksheets("MySheetName")
colCount = sht.ListObjects(1).ListColumns.Count + 1
sht.ListObjects(1).ListColumns.Add (colCount)
sht.ListObjects(1).ListColumns(colCount).Name = "Criteria"
With sht.ListObjects(1).ListColumns(colCount).DataBodyRange.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Item One, Item Two, Item Three, Item Four"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub
Upvotes: 0
Reputation: 2741
I think I see what you are after. This will create a Validation List and populate the list with values of you choice.
Dim myRng As String
myRngr = Range("A1:A10").Address
With Range("B2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=" & myRngr & ""
End With
To amend what range to what source data you need, you just need to change this line,
myRngr = Range("A1:A10").Address
Then you could use a loop to run through the L column
and if a criteria is met add a validation list.
Hope that helps.
Upvotes: 1