Help with Worksheet_Change;section of code not woring as expected

A

Ayo

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
 
A

Ayo

Disregard Post. Found the problem.

Ayo said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

i to = column in Formula 19
Hiding Rows and Columns in code 5
I need help with this. 2
Find the Empty Cell Macro 9
VBA triggers on tabkey 8
Macro to set new data validation each time 2
VBA dropdownlist to put in cell's comment creates runtime error whenthe dropdownlist is above 266 li 0
Dropdown in cell base on a selection of a different drop down 3

Top