Hi All,

I am new to excel macros and VBA or VBS. Just wondering whether my thought is possible at all.
My requirement is --
I have a excel sheet wchich have several rows having different background colors. They all corresponds to say about 50 different items allotted to some 10 to 15 projects. A project can have multiple items allotted.

Below the allotment area spanning 50 rows, at about say from 60th row, the 15 project names are specified along with their corresponding colors.(first column the color, the 2nd column the name)

Now I want to have the second column of the second row to have a dropdown box having all the names of the projects. (the first row is left for headings, the first column for the item names, the columns from 3 onwards represents each week hence the extend of color determines for how many weeks that item is allotted to which project.)

I got the dropdown list in some sort of twisted way using the validation feature. Now, I want that whenever I select one value from this dropdown, it should match the color code present down below (the 60th row onwards) and then filter those rows with the same colored cells.
So, say I select project B having color code red and say I have three items allotted for project B, then as soon as I select project B from the drop down, those three rows should only be shown, rest hidden.

Is this possible? It seems a bit more dynamic than usual macro stuff. :-(

Some very naive code that I came upon --
Sub mydrpdwn()

ActiveWorkbook.Names.Add Name:="MyList", RefersTo:="=allocation_tracker!$b$99:$b$113"

With Worksheets("allocation_tracker").Range("D2").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=MyList"

End With

MsgBox "i am here 1"
End Sub
Sub drpdwnslct()
Dim rowsToCheck As Range
With ActiveSheet
Set rowsToCheck = .Range(Range("B99"), Range("B99").End(xlDown))
End With

MsgBox "i am here 2"

Dim cell As Range
For Each cell In rowsToCheck

If cell.Value = ActiveCell.Select Then
MsgBox "i am here 3"
End If

Next cell

End Sub