Vba macros

[saurabhlotankar]

Hi all,

I have an excel file with 4 columns. Columns names are No, Work, First name and time required respectively.

I have given sample data below.

No	Work	First name	Time required
1	Work1	A_1	106
2	Work1	A_2	97
3	Work2	A_3	90
4	Work2	A_4	20
5	Work2	A_5	109
6	Work2	A_6	104
7	Work2	A_7	34
8	Work2	A_8	37
9	Work3	A_9	89
10	Work3	A_10	77
11	Work3	A_11	62
12	Work3	A_12	116

<colgroup><col span="4"></colgroup><tbody>
</tbody>

I want 3 simple macros.

First one is, I want to create a dropdown button from active controlX and assign a macro to it. Dropdown should have list of works mentioned in column B (work). And whenever I will select any value ( any work) from the dropdown it should automatically color code rows with same work name in column B.

Second is, Create Text Box from active contolX. It should Filter the list in column "D" to display all records where " Time Required" is greater than the number mentioned in the textbox object.

It would be great if you could help me with this.

Thanks.

 

[MrJoosten]

Sub macro1()
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
Range("A1:D" & lr).AutoFilter
Range("B1:B" & lr).Copy
Range("I1:I" & lr).Select
ActiveSheet.Paste
ActiveSheet.Range("I1:I" & lr).RemoveDuplicates Columns:=1, Header:=xlNo
Cells(2, 5).Activate
With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=I2:I" & lr
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
End With

End Sub

Sub macro2()
Dim lr As Long
Dim inputValue As Long
Dim filterRng As Range
inputValue = InputBox("fill in the minimum required time", "Req time")
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set filterRng = Range("A1:D" & lr)
filterRng.AutoFilter Field:=4, Criteria1:=">" & inputValue, Operator:=xlFilterValues
End Sub

Sub macro3()
Dim lr As Long
Dim filterRng As Range

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set filterRng = Range("A1:D" & lr)
filterRng.AutoFilter Field:=2, Criteria1:=Range("E2").Value, Operator:=xlFilterValues

end sub

I'm still new, but hope I could help you a little bit.