Worksheet_Change(ByVal Target As Range) is very slow

Babu

New Member
Joined
Jun 4, 2003
Messages
4
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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:

Code:
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

New Member
Joined
Jun 4, 2003
Messages
4
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
 

Forum statistics

Threads
1,137,298
Messages
5,680,684
Members
419,924
Latest member
Dhamodharan992

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top