Sub ValidationUpdate()
Dim wbThis As Workbook
' Code designed to modify the validation formula that exists in 40 specific cells within the worksheet.
' Modification needed is to simply add a "TypeFive" to the series of match types in the MATCH formula.
' Note that the cell containing the lookup value in each formula is always two cells above
' the cell that contains the validation list formula.
wbThis.Worksheets("Section Updates").Activate
' Section 1 (the drop-down list displayed in cell F22 is based on the selection made from the drop-down list in F20)
Range("F22").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=CHOOSE(MATCH($F$20,SectionType,0),TypeOne,TypeTwo,TypeThree,TypeFour,TypeFive)" 'TypeOne thru TypeFour already exist; TypeFive needs to be added
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please select response from the drop-down list provided"
.ShowInput = True
.ShowError = True
End With
' Section 2
Range("F54").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:= _
"=CHOOSE(MATCH($F$52,SectionType,0),TypeOne,TypeTwo,TypeThree,TypeFour,TypeFive)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = "Please select response from the drop-down list provided"
.ShowInput = True
.ShowError = True
End With
' Sections 3 thru 40 must also be updated, with the cell range to be selected for updating as follows
' All 40 cells containing a validation formula that needs to be updated:
' [F22,F54,F86,F118,F150,F182,F214,F246,F278,F310,F342,F374,F406,F438,F470,F502,F534,F566,F598,F630,F662,F694,
' F726,F758,F790,F822,F854,F886,F918,F950,F982,F1014,F1046,F1078,F1110,F1142,F1174,F1206,F1238,F1270]
End Sub
Bookmarks