Need help changing cell value when another value changes

[Kevlar]

I have this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = ("$D$5") Then
If Range("$Q$9").Value <> "CA" Then
Range("$N$32").Value = "Out of State"
Else: Range("$N$32").Value = "In-State"
End If
End If

End Sub

I want the value in cell N32 to change based on what I have in cell Q
when I enter data into cell D5 and press enter.

It only works after I click on cell D5 (after changing d5 and pressin
enter)

What am i missing

 

[Trevor Shuttleworth]

You want Worksheet_Change rather than Worksheet_SelectionChange

Regards

Trevor

 

[Jack Schitt]

Try putting your code in

Private Sub Worksheet_Change(ByVal Target As Range)

instead of in

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

 

[Kevlar]

That worked but to get the correct result I had to change <> to =
Hmmmm, wonder why

 

[Trevor Shuttleworth]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$D$5")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If UCase(Range("$Q$9").Value) <> "CA" Then
Range("$N$32").Value = "Out of State"
Else
Range("$N$32").Value = "In-State"
End If
Application.EnableEvents = True
End Sub

Regards

Trevor