Question on using Select Case

[BHomestar]

Hi everyone,

I was hoping someone could look at my code below and tell me how to leave cell(-1,1) unchanged if the three cases are not met. Right now the cell is turned to blank if the case is not met. Also, how can I change the activation of the procedure to clicking on cell C15 specifically instead of just any selection change? Would this be easier with an IF THEN?

Thanks for any help!!

Bryan


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cell As Range, answer As String
Set rng = Range([C15], [C15])
For Each cell In rng
Select Case cell
Case Is = "Fixed 30 Year": answer = "Not Applicable"
Case Is = "Fixed 20 Year": answer = "Not Applicable"
Case Is = "Fixed 15 Year": answer = "Not Applicable"
End Select
cell(-1, 1) = answer
n:
Next
End Sub

 

[Legacy 21301]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim answer As String
If Target.Address = "$C$15" Then
Select Case Target
Case Is = "Fixed 30 Year": answer = "Not Applicable"
Case Is = "Fixed 20 Year": answer = "Not Applicable"
Case Is = "Fixed 15 Year": answer = "Not Applicable"
Case Else: answer = Target(-1, 1)
End Select
Target(-1, 1) = answer
End If
End Sub

 

[BHomestar]

Thank You Ponsy Nob!!!

 

[parry]

Another option is to use the change event if the value of C15 is changed.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$15" And Target.Cells.Count = 1 Then
   If Target = "Fixed 30 Year" Or Target = "Fixed 20 Year" _
   Or Target = "Fixed 15 Year" Then
   Target(-1, 1) = "Not Applicable"
   End If
End If
End Sub

 

[BHomestar]

Thank you Parry!!