Worksheet_Change(ByVal Target As Range) is very slow

[Babu]

I wanted the cell colors to change according to the value it hold. So I have used the following code in my excel sheet. The code works, but it is very slow.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = True
If Target.Column = 31 Or Target.Column = 34 Or Target.Column = 37 Or Target.Column = 40 Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
Target = UCase(Target)
If Target.Value = "P" Then Target.Interior.ColorIndex = 4
If Target.Value = "F" Then
Target.Interior.ColorIndex = 3
Target.Offset(0, 1).Select
End If
If Target.Value = "B" Then
Target.Interior.ColorIndex = 24
Target.Offset(0, 1).Select
End If
If Target.Value = "N" Then Target.Interior.ColorIndex = 6
End If
End Sub

Can anyone help me in improving the speed or optimizing the code.

Thanks in Advance,
Saravana Babu Srinivasan

 

[Richard Schollar]

Hi Saravana

Use of Select (when concerned with ranges) can certainly slow things down. Try the following code and see if it makes any difference:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("AE:AE,AH:AH,AK:AK,AN:AN")) Is Nothing Then
    If Target.Cells.Count = 1 Then
        With Target
            Select Case UCase(Target.Value)
                Case "P"
                    .Interior.ColorIndex = 4
                Case "F"
                    .Interior.ColorIndex = 3
                Case "B"
                    .Interior.ColorIndex = 24
                Case "N"
                    .Interior.ColorIndex = 6
                Case Else
            End Select
        End With
    End If
End If
End Sub

Best regards

Richard

 

[Babu]

Hi Richard,

Thanks a lot. Your code works faster than my code.

I did a little bit research on that and found that converting the value from lower case to upper case is what causing the delay. I added the same code

Target = UCase(Target) to your code snippet and found that it slowed down.

Thanks and regards,
Saravana Babu Srinivasan