Slow code
 

I have the following fairly simple code which runs fairly slowly, could it
be because I have a lot of Conditional Formatting going on at the same time?
and if so , would I be better incorporating the conditional formatting into
my code to acheive better processing?

Sandy

Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("Data Input").Unprotect Password:=""

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each mycell In Range("C9:K9,M9:U9")
If mycell.Value = 3 Then
With mycell.Offset(5)
.Value = "Good"
.Validation.Delete
End With
ElseIf mycell.Value < 3 And mycell.Offset(5).Value = "Good" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
ElseIf mycell.Value = "" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If
Next

'MsgBox "Hi"

Application.EnableEvents = True
Application.ScreenUpdating = True

Sheets("Data Input").Protect Password:=""

End Sub

Slow code
 

Sandy

It would appear as if this is the case - conditional formatting slowing
down the code.I tried it on my pc and it ran at normal speed.

As you susgessted, try coding the conditional format into VBA and see if
that enhances the speed of the application.

Also I answered your previous query in relation to running VBA code -
conditional formatting update - on the hour (please refer to the previous
thread for the solution).

Regards

SysAccountant

"Sandy" wrote:

I have the following fairly simple code which runs fairly slowly, could it
be because I have a lot of Conditional Formatting going on at the same time?
and if so , would I be better incorporating the conditional formatting into
my code to acheive better processing?

Sandy

Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("Data Input").Unprotect Password:=""

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each mycell In Range("C9:K9,M9:U9")
If mycell.Value = 3 Then
With mycell.Offset(5)
.Value = "Good"
.Validation.Delete
End With
ElseIf mycell.Value < 3 And mycell.Offset(5).Value = "Good" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
ElseIf mycell.Value = "" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If
Next

'MsgBox "Hi"

Application.EnableEvents = True
Application.ScreenUpdating = True

Sheets("Data Input").Protect Password:=""

End Sub

Slow code
 

Thanks I think I'll do as you suggest and incorporate the conditional
formatting in code. I'll let you know here how it goes.
The other post is for a different Sandy - must be a common Scottish name
after all :)
Sandy

"SysAccountant" wrote in message
...
Sandy

It would appear as if this is the case - conditional formatting slowing
down the code.I tried it on my pc and it ran at normal speed.

As you susgessted, try coding the conditional format into VBA and see if
that enhances the speed of the application.

Also I answered your previous query in relation to running VBA code -
conditional formatting update - on the hour (please refer to the previous
thread for the solution).

Regards

SysAccountant

"Sandy" wrote:

I have the following fairly simple code which runs fairly slowly, could
it
be because I have a lot of Conditional Formatting going on at the same
time?
and if so , would I be better incorporating the conditional formatting
into
my code to acheive better processing?

Sandy

Private Sub Worksheet_Change(ByVal Target As Range)

Sheets("Data Input").Unprotect Password:=""

Application.ScreenUpdating = False
Application.EnableEvents = False

For Each mycell In Range("C9:K9,M9:U9")
If mycell.Value = 3 Then
With mycell.Offset(5)
.Value = "Good"
.Validation.Delete
End With
ElseIf mycell.Value < 3 And mycell.Offset(5).Value = "Good" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
ElseIf mycell.Value = "" Then
With mycell.Offset(5)
.Value = "Hit"
With .Validation
.Delete
.Add Type:=xlValidateList,
Formula1:="Left,Right,Short,Long"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If
Next

'MsgBox "Hi"

Application.EnableEvents = True
Application.ScreenUpdating = True

Sheets("Data Input").Protect Password:=""

End Sub