I am trying to change data vailidation settings on Sheet1 based on a state name provided on a separate worksheet (Sheet2). The below code is part of a macro that is assigned to a button on Sheet2. The issue is when the state name changes on Sheet2 and the button is pushed to initiate the macro to run, the state validation does not change on Sheet1.
If ("F5") <> "California" Then
GoTo Colorado:
Else
Sheets("Sheet1").Select
ActiveSheet.unprotect pw
Range("C8:C40201").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$H$40204:$H$40219"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Input Error"
.InputMessage = ""
.ErrorMessage = "Category description must be from the list provided."
.ShowInput = True
.ShowError = True
End With
End If
Colorado:
If ("F5") <> "Colorado" Then
GoTo StateEnd:
Else
Sheets("Sheet1").Select
ActiveSheet.unprotect pw
Range("C8:C40201").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$H$40220:$H$40229"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Input Error"
.InputMessage = ""
.ErrorMessage = "Category description must be from the list provided."
.ShowInput = True
.ShowError = True
End With
End If
StateEnd:
Any help would be greatly appreciated.
Cheers,
C
Bookmarks