The last if statement block in this subroutine: "If Target.Address() =
"$F$4"" Then is not responding. Can someone take a look and give me a hand
please? Nothing happens when I change the cell value from Yes to No or the
other way around.
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim marketWS As Worksheet, rngmarketWS As Range

Set marketWS = Worksheets("Market_NLP Data")
Set rngmarketWS = marketWS.Range("A6:BG500")

If Target.Address() = "$A$2" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
rngmarketWS.Select
With Selection
.ClearContents
.Interior.ColorIndex = 0
.Font.ColorIndex = 1
End With
Range("M1") = ""
Range("E2") = ""
Range("E2").Select

Select Case Target.Value

Case "CENTRAL PA"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP2,NLP3"
End With

Case "CONNECTICUT"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP2,NLP3"
End With

Case "LONG ISLAND - NY"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP3"
End With

Case "NEW ENGLAND MARKET"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP2,NLP3"
End With

Case "NEW JERSEY NJ"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP3"
End With

Case "NEW YORK NY"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1 Infill"
End With

Case "NY (UPSTATE)"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP2,NLP3"
End With

Case "PHILDELPHIA PA"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP2,NLP3"
End With

Case "VIRGINIA"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP2,NLP3"
End With

Case "WASHINGTON DC"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP2,NLP3"
End With
End Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

If Target.Address() = "$E$2" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
rngmarketWS.Select
With Selection
.ClearContents
.Interior.ColorIndex = 0
.Font.ColorIndex = 1
End With
Call copyMarketData(Range("A2").Value, Target.Value)
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

If Target.Address() = "$F$4" Then
'Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Value = "Yes" And Sheets("Data Corrections").Visible = False
Then
Sheets("Data Corrections").Visible = True
ElseIf Target.Value = "No" And Sheets("Data Corrections").Visible = True
Then
Sheets("Data Corrections").Visible = False
End If
'Application.EnableEvents = True
Application.ScreenUpdating = True
End If

End Sub

Disregard Post. Found the problem.

"Ayo" wrote:

The last if statement block in this subroutine: "If Target.Address() =
"$F$4"" Then is not responding. Can someone take a look and give me a hand
please? Nothing happens when I change the cell value from Yes to No or the
other way around.
Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim marketWS As Worksheet, rngmarketWS As Range

Set marketWS = Worksheets("Market_NLP Data")
Set rngmarketWS = marketWS.Range("A6:BG500")

If Target.Address() = "$A$2" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
rngmarketWS.Select
With Selection
.ClearContents
.Interior.ColorIndex = 0
.Font.ColorIndex = 1
End With
Range("M1") = ""
Range("E2") = ""
Range("E2").Select

Select Case Target.Value

Case "CENTRAL PA"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP2,NLP3"
End With

Case "CONNECTICUT"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP2,NLP3"
End With

Case "LONG ISLAND - NY"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP3"
End With

Case "NEW ENGLAND MARKET"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP2,NLP3"
End With

Case "NEW JERSEY NJ"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP3"
End With

Case "NEW YORK NY"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1 Infill"
End With

Case "NY (UPSTATE)"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP2,NLP3"
End With

Case "PHILDELPHIA PA"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP2,NLP3"
End With

Case "VIRGINIA"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP2,NLP3"
End With

Case "WASHINGTON DC"
With Range("E2").Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertInformation, Formula1:="NLP1
Infill,NLP2,NLP3"
End With
End Select
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

If Target.Address() = "$E$2" Then
Application.EnableEvents = False
Application.ScreenUpdating = False
rngmarketWS.Select
With Selection
.ClearContents
.Interior.ColorIndex = 0
.Font.ColorIndex = 1
End With
Call copyMarketData(Range("A2").Value, Target.Value)
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

If Target.Address() = "$F$4" Then
'Application.EnableEvents = False
Application.ScreenUpdating = False
If Target.Value = "Yes" And Sheets("Data Corrections").Visible = False
Then
Sheets("Data Corrections").Visible = True
ElseIf Target.Value = "No" And Sheets("Data Corrections").Visible = True
Then
Sheets("Data Corrections").Visible = False
End If
'Application.EnableEvents = True
Application.ScreenUpdating = True
End If

End Sub