Changing a SelectionChange range

[bademployee]

I'm using the following routine, but Z only updates when A12:A21 are selected.

I need to change this so that A12:A21 change when any within the sheet is selected.

[FONT=Verdana]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/FONT]    
    
    Dim Cell    As Range
    Dim Rng     As Range
    
        If Target.Cells.Count > 1 Then Exit Sub
    
        Set Rng = Range("A12:A21")
    
        If Intersect(Target, Rng) Is Nothing Then Exit Sub
    
        Application.EnableEvents = False
        
            Set Cell = Cells(Target.Row, "Z")
        
            Select Case Target.Value
                Case Is = "UAN": Cell.Value = "Variable"
                Case Is = "": Cell.Value = ""
                Case Else: Cell.Value = "Fixed"
            End Select
            
        Application.EnableEvents = True
[FONT=Verdana]
End Sub[/FONT]

Thanks in advance

Mark

 

[bademployee]

Edit - I need to change the code above so that column Z updates when any cell is selected.

Thanks

 

[JoeMo]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim Cell    As Range
        If Target.Cells.Count > 1 Then Exit Sub
    
        Application.EnableEvents = False
        
            Set Cell = Cells(Target.Row, "Z")
        
            Select Case Target.Value
                Case Is = "UAN": Cell.Value = "Variable"
                Case Is = "": Cell.Value = ""
                Case Else: Cell.Value = "Fixed"
            End Select
            
        Application.EnableEvents = True

End Sub

 

[bademployee]

Try:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Dim Cell    As Range
        If Target.Cells.Count > 1 Then Exit Sub
    
        Application.EnableEvents = False
        
            Set Cell = Cells(Target.Row, "Z")
        
            Select Case Target.Value
                Case Is = "UAN": Cell.Value = "Variable"
                Case Is = "": Cell.Value = ""
                Case Else: Cell.Value = "Fixed"
            End Select
            
        Application.EnableEvents = True

End Sub

Thanks Joe, works perfectly!

 

[JoeMo]

You are welcome - thanks for the reply.