Greying out and disabling cells based on drop down selection

**RichieSams2** · 03-24-2011, 05:08 PM

Hi,

I searched the forums for the code necessary and I modified it to my use:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Value = Worksheets("Gear Choice").Range("B2") Then
        If VLookup(Target.Value, Worksheets("Gear list").Range("A3:H12"), 8, False) Then
            Worksheets("Gear Choice").Range("B4:H5").Interior.Color = RGB(192, 192, 192)
        Else
            Worksheets("Gear Choice").Range("B4:B5").Interior.Color = RGB(255, 255, 153)
            Worksheets("Gear Choice").Range("C4:H5").Interior.ColorIndex = xlNone
        End If
    End If
End Sub

This code is placed in the "Gear Choice" Sheet code. As the title suggests, I want to disable and gray out the 2 rows below a drop down cell if the vlookup returns true. My problem is that when I try and change something in the excel document to run this code, it throws an error saying, "Compile error. Sub or Function is not defined", pointing to the first line of the code. At first I thought this was a scope issue, so I transferred the code to the "ThisWorkbook" code area using Workbook_SheetChange syntax instead of Worksheet_Change syntax. This didn't work either. It still throws the same error. Attached is my project.

Thanks,

RichieSams