Speeding up a selection change routine

[bademployee]

Hi all,

Is it possible to speed up the following routine:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("a12") = "UAN" Then
        Range("z12") = "Variable"
    ElseIf Range("a12") = "" Then
        Range("z12") = ""
    Else
        Range("z12") = "Fixed"
    End If


    If Range("a13") = "UAN" Then
        Range("z13") = "Variable"
    ElseIf Range("a13") = "" Then
        Range("z13") = ""
    Else
        Range("z13") = "Fixed"
    End If


    If Range("a14") = "UAN" Then
        Range("z14") = "Variable"
    ElseIf Range("a14") = "" Then
        Range("z14") = ""
    Else
        Range("z14") = "Fixed"
    End If


    If Range("a15") = "UAN" Then
        Range("z15") = "Variable"
    ElseIf Range("a15") = "" Then
        Range("z15") = ""
    Else
        Range("z15") = "Fixed"
    End If


    If Range("a16") = "UAN" Then
        Range("z16") = "Variable"
    ElseIf Range("a16") = "" Then
        Range("z16") = ""
    Else
        Range("z16") = "Fixed"
    End If


    If Range("a17") = "UAN" Then
        Range("z17") = "Variable"
    ElseIf Range("a17") = "" Then
        Range("z17") = ""
    Else
        Range("z17") = "Fixed"
    End If


    If Range("a18") = "UAN" Then
        Range("z18") = "Variable"
    ElseIf Range("a18") = "" Then
        Range("z18") = ""
    Else
        Range("z18") = "Fixed"
    End If


    If Range("a19") = "UAN" Then
        Range("z19") = "Variable"
    ElseIf Range("a19") = "" Then
        Range("z19") = ""
    Else
        Range("z19") = "Fixed"
    End If


    If Range("a20") = "UAN" Then
        Range("z20") = "Variable"
    ElseIf Range("a20") = "" Then
        Range("z20") = ""
    Else
        Range("z20") = "Fixed"
    End If


    If Range("a21") = "UAN" Then
        Range("z21") = "Variable"
    ElseIf Range("a21") = "" Then
        Range("z21") = ""
    Else
        Range("z21") = "Fixed"
    End If
End Sub

Cheers

 

[Leith Ross]

Hello bademployee,

Give this a go...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    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
        
End Sub

 

[bademployee]

Thanks Leith,

But unfortunately, nothing is showing in Z

A12:A21 are validation lists...this shouldn't matter as long as there's a value there - is this correct?

 

[bademployee]

Hello bademployee,

Give this a go...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    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
        
End Sub

Sorry Leith, my bad.

Your code works just fine, I've got other code interfering somewhere.

One thing I noticed is after updating a value in A12:A21 the user then needs to re-select the same cell for Z to update - can this be changed so as Z updates regardless of what cell is selected?

Cheers