Excel 2003 - Creating Named ranges for a validation list box using a case statement

Hi,,

It might need a macro but upload your workbook and note in column D which list you expect to see.

I think Ive cracked it!!!

Sub Test_Validation_List_With_If_Statement_3()
'
' Macro recorded 01/03/2013 by A2701687
' This Macro is Used within the XLS on its own when the school is happy that they have filled the NewPosts column in

'Go back to sheet 1 from the instructions sheet
    Sheets(1).Select 'This selects sheet 1 no matter what it is called
     Range("D2").Select 'This takes you to the NewPost Column that is needed to be sorted out.
         
'Brand new section. we dont want them to protect if they havent done everything neccessary

    Dim i As Long, lrow As Long ', Lastrow As Long

With Worksheets(1)
    lrow = .Range("C" & .Rows.Count).End(xlUp).Row
    
    'For To Clause. When the counter i is at 2 (The first actual value. Remember the first few rows are taken up with the button
    'to the number of the row we are on.
    'The final value of the of the i counter in the above loop will be after 1row.
    ' i starts on row 2 and the 1row

    
    For i = 2 To lrow
    'If Job_Family_Desc = AA use the AA range as a validation list
            If .Range("C" & i).Value = "AA" Then

                    With Selection.Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=JobFamilyAA"
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .InputTitle = ""
                        .ErrorTitle = ""
                        .InputMessage = ""
                        .ErrorMessage = ""
                        .ShowInput = True
                        .ShowError = True
                    End With
                    
                    'And when you have done it you want to go down to the next row
                    'Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
                    Selection.Offset(1, 0).Select

           'Exit Sub If you use that at this point you go right to the top of the sub again

    'If Job_Family_Desc = BB use the BB range as a validation list
            ElseIf .Range("C" & i).Value = "BB" Then

                    With Selection.Validation
                        .Delete
                        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                        xlBetween, Formula1:="=JobFamilyBB"
                        .IgnoreBlank = True
                        .InCellDropdown = True
                        .InputTitle = ""
                        .ErrorTitle = ""
                        .InputMessage = ""
                        .ErrorMessage = ""
                        .ShowInput = True
                        .ShowError = True
                    End With
                    
                    'And when you have done it you want to go down to the next row
                   
                    'Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
                    Selection.Offset(1, 0).Select
           'Exit Sub If you use that at this point you go right to the top of the sub again
         
    'If Job_Family_Desc = CC use the CC range as a validation list
            ElseIf .Range("C" & i).Value = "CC" Then

                With Selection.Validation
                    .Delete
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                    xlBetween, Formula1:="=JobFamilyCC"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
                
                    'And when you have done it you want to go down to the next row

                    'Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
                    Selection.Offset(1, 0).Select
                    
           'Exit Sub If you use that at this point you go right to the top of the sub again

    'If Job_Family_Description Is empty then the file is done so we can come out of this IF THEN ELSE Condition
            ElseIf .Range("C" & i).Value = "" Then
            
            
            Exit Sub
        End If
    Next i
End With

End Sub